How to delete a data from a table and all the references?

Question

I need to DELETE a consumer from the "Customer" table and all the data related to him from the "ContactDetails", "Invoice" and "ServiceTicket" tables.

Here is the tables:

CREATE TABLE ContactDetail (
    DetailID integer IDENTITY(1,1) PRIMARY KEY,
    FirstName varchar(40) NOT NULL,
    SurName varchar(40) NOT NULL,
    DoB date NOT NULL,
    Address01 varchar(80) NOT NULL,
    Address02 varchar(80),
    City varchar(40) NOT NULL,
    County varchar(40) NOT NULL,
    EirCode varchar(8) NOT NULL,
    Email varchar(40),
    PhoneNumber varchar(20),
    MobileNumber varchar(20) NOT NULL
);

CREATE TABLE Customer (
    CustomerID varchar(9) PRIMARY KEY,
    DetailID integer FOREIGN KEY REFERENCES ContactDetail(DetailID),
    LastActivity date NOT NULL,
    CustumerStatus varchar(9) NOT NULL
);

CREATE TABLE SalesPerson (
    SalesPersonID varchar(9) PRIMARY KEY,
    DetailID integer FOREIGN KEY REFERENCES ContactDetail(DetailID)
);

CREATE TABLE Mechanic (
    MechanicID varchar(9) PRIMARY KEY,
    DetailID integer FOREIGN KEY REFERENCES ContactDetail(DetailID)
);

CREATE TABLE Car (
    CarSerialNumber varchar(12) PRIMARY KEY,
    CarIsForSale varchar(1) NOT NULL,
    CarCondition varchar(1) NOT NULL,
    CarMiliage integer,
    CarMake varchar(40),
    CarModel varchar (40),
    CarYear date,
    CarColor varchar (20),
    CarTransmission varchar (10),
    CarEngineSize varchar (10),
    CarFuelType varchar (10),
    CarDoors integer,
    CarBodyStyle varchar (20),
    CarSalePrice decimal (19,4)
);

CREATE TABLE Invoice (
    InvoiceNumber integer IDENTITY(1,1) PRIMARY KEY,
    SalesPersonID varchar(9) FOREIGN KEY REFERENCES SalesPerson(SalesPersonID),
    CustomerID varchar(9) FOREIGN KEY REFERENCES Customer(CustomerID),
    CarSerialNumber varchar(12) FOREIGN KEY REFERENCES Car(CarSerialNumber),
    InvoiceDate datetime NOT NULL,
);

CREATE TABLE ServiceTicket (
    ServiceNumber varchar(9) PRIMARY KEY,
    MechanicID varchar(9) FOREIGN KEY REFERENCES Mechanic(MechanicID),
    CustomerID varchar(9) FOREIGN KEY REFERENCES Customer(CustomerID),
    CarSerialNumber varchar(12) FOREIGN KEY REFERENCES Car(CarSerialNumber),
    ServiceTicketDate date NOT NULL,
    ServiceDue date NOT NULL,
    ServiceDescription varchar (80) NOT NULL,
    ServicePrice decimal(19,4) NOT NULL,
);

Add tag for database used. Does database have relational integrity and cascade delete features set (like can be done in Access). Run DELETE action SQL for each table. Why even delete data? — June7, Dec 02 '21 at 21:40
You can't delete rows from multiple tables with a single query. I would suggest using a DELETE trigger on your `Customer` table that deletes the other entries from their respective tables. Refer to [this answer](https://stackoverflow.com/a/9996970/10601203) for a general idea of how this would work. — Jesse, Dec 02 '21 at 21:43
Well, you could change the design and reference `customer` in `contactdetail` instead of the other way round and have the actions of the foreign keys referencing `customer` set to `ON DELETE CASCADE`. Then this would automatically delete all related data when a customer is deleted. — sticky bit, Dec 02 '21 at 21:53

Pugzly · Answer 1 · 2021-12-05T18:29:31.630

As @stickybit said you can use cascade delete if you are setup properly. Below is a test case.

create table parent (
  id NUMBER(10),
  value      varchar2(30),
constraint parent_pk primary key (id)
);

CREATE TABLE child
( id NUMBER(10) not null,
value NUMBER(10) not null,
constraint child_pk primary key (id,value),
CONSTRAINT parent_child_fk
FOREIGN KEY (id)
REFERENCES parent(id)
ON DELETE CASCADE
);

CREATE TABLE grandchild
( id NUMBER(10) not null,
value NUMBER(10) not null,
constraint grandchild_pk primary key (id,value),
CONSTRAINT child_grandchild_fk
FOREIGN KEY (id,value)
REFERENCES child(id,value)
ON DELETE CASCADE
);


insert into parent values (1,'a');
insert into parent values (2,'b');
insert into parent values (3,'c');

insert into child  values (1,1);
insert into child  values (1,2);
insert into child  values (1,3);
insert into child  values (2,1);
insert into child  values (2,2);
insert into child  values (2,3);
insert into child  values (3,1);
insert into child  values (3,2);
insert into child  values (3,3);

insert into grandchild  values (1,1);
insert into grandchild  values (1,2);
insert into grandchild  values (1,3);
insert into grandchild  values (2,1);
insert into grandchild  values (2,2);
insert into grandchild  values (2,3);
insert into grandchild  values (3,1);
insert into grandchild  values (3,2);
insert into grandchild  values (3,3);

SELECT  (
        SELECT COUNT(*)
        FROM   parent 
        ) AS parent_cnt,
       (
        SELECT COUNT(*)
        FROM   child 
        ) AS child_cnt,
        (
        SELECT COUNT(*)
        FROM   grandchild
        ) AS grandchild_cnt
FROM    dual

PARENT_CNT  CHILD_CNT   GRANDCHILD_CNT
3   9   9


DELETE from parent where value = 'a';

SELECT  (
        SELECT COUNT(*)
        FROM   parent 
        ) AS parent_cnt,
       (
        SELECT COUNT(*)
        FROM   child 
        ) AS child_cnt,
        (
        SELECT COUNT(*)
        FROM   grandchild
        ) AS grandchild_cnt
FROM    dual

PARENT_CNT    CHILD_CNT    GRANDCHILD_CNT
2    6    6

Here is a query you can use to show the relationships.


with f as (
        select constraint_name, table_name, r_constraint_name
        from   user_constraints
        where  constraint_type = 'R'
     ),
     p as (
        select constraint_name, table_name
        from   user_constraints
        where  constraint_type = 'P'
     ),
     j (child_table, f_key, parent_table, p_key) as (
        select f.table_name, f.constraint_name, p.table_name, f.r_constraint_name
        from   p join f on p.constraint_name = f.r_constraint_name
        union all
        select 'PARENT', (select constraint_name from p                                where table_name = 'PARENT'), null, null from dual
     )
select level as lvl, j.*
from j
start with parent_table is null
connect by nocycle parent_table = prior child_table
order by lvl, parent_table, child_table;

So now when we delete from parent Oracle will check if there are tables with FKs referencing parent and will find table child. Then it will check one-by-one if to be deleted table parent row has children in table child. If not it will delete that table parent row. If there are children if will check FK action and since that FK has ON DELETE CASCADE Oracle will try to delete that table child row. At that point it will (same as with table parent) check if there are tables with FKs referencing table child and will find table granchild. Same way it will check one-by-one if to be deleted table child row has children in table grandchild. If not it will delete that table child row. If there are children if will check FK action and since that FK has ON DELETE CASCADE Oracle will try to delete that table grandchild row. Since table grandchild has no child tables Oracle will delete corresponding rows in tables grandchild, child and then parent.

How to delete a data from a table and all the references?

1 Answers1