Our system has a table that is a nominal parent to several other tables. Each of these relationships are 1-1, and there will only be one child per parent. We need to ensure that when deleting a child row that the parent is always deleted as well. Is there a reasonable alternative to a trigger in this case?
(Live SQL is currently unavailable)
create table MY_PARENT_TABLE (
PK number(19) primary key,
CUSTOMER_ID number(19),
CREATE_DATETIME timestamp(6),
DESCRIPTION varchar2(500 char),
ACTION_TYPE varchar2(40 char)
);
create table MY_CHILD_TABLE_DOCUMENT_ATTACHMENT (
PK number(19) primary key,
DOCUMENT_ID number(19),
foreign key (PK) references MY_PARENT_TABLE (PK)
);
create table MY_CHILD_TABLE_CALENDAR_ALERT (
PK number(19) primary key,
CALENDAR_ID number(19),
APPOINTMENT date,
foreign key (PK) references MY_PARENT_TABLE (PK)
);
create table MY_CHILD_TABLE_ORDER_REVIEW (
PK number(19) primary key,
ORDER_ID number(19),
RESULTS varchar2(10 char),
foreign key (PK) references MY_PARENT_TABLE (PK)
);
You by adding a foreign key from the child to the parent. Which is:
- on delete cascade
- deferrable initially deferred
create table MY_PARENT_TABLE (
PK number(19) primary key,
CUSTOMER_ID number(19),
CREATE_DATETIME timestamp(6),
DESCRIPTION varchar2(500 char),
ACTION_TYPE varchar2(40 char)
);
create table MY_CHILD_TABLE_DOCUMENT_ATTACHMENT (
PK number(19) primary key,
DOCUMENT_ID number(19),
foreign key (PK) references MY_PARENT_TABLE (PK)
on delete cascade
);
alter table MY_PARENT_TABLE
add constraint fk
foreign key ( pk )
references MY_CHILD_TABLE_DOCUMENT_ATTACHMENT ( pk )
on delete cascade
deferrable initially deferred;
insert into MY_PARENT_TABLE
values ( 1, 1, sysdate, null, null );
insert into MY_CHILD_TABLE_DOCUMENT_ATTACHMENT
values ( 1, 1 );
commit;
select count(*) from my_parent_table;
COUNT(*)
1
delete MY_CHILD_TABLE_DOCUMENT_ATTACHMENT;
select count(*) from my_parent_table;
COUNT(*)
0 Of course, this only works if there's only one child. Or the parent row has a row in every child.
You can get around this by adding:
- A TYPE column to the parent (& child) tables
- A virtual column to the parent for each child table. This inspects the TYPE column and returns the PK as appropriate. Otherwise null
- An on delete cascade, deferrable, initially deferred foreign key on these VCs to the appropriate child table
Which looks like:
drop table MY_PARENT_TABLE cascade constraints purge;
drop table MY_CHILD_TABLE_DOCUMENT_ATTACHMENT cascade constraints purge;
drop table MY_CHILD_TABLE_CALENDAR_ALERT cascade constraints purge;
create table MY_PARENT_TABLE (
PK number(19) primary key,
tab_type varchar2(10) not null,
CUSTOMER_ID number(19),
CREATE_DATETIME timestamp(6),
DESCRIPTION varchar2(500 char),
ACTION_TYPE varchar2(40 char),
unique ( tab_type, pk )
);
create table MY_CHILD_TABLE_DOCUMENT_ATTACHMENT (
PK number(19) primary key,
tab_type varchar2(10) not null
check ( tab_type = 'DOCUMENT' ),
DOCUMENT_ID number(19),
foreign key (PK) references MY_PARENT_TABLE (PK)
on delete cascade,
foreign key (PK, tab_type) references MY_PARENT_TABLE (PK, tab_type)
on delete cascade,
unique ( tab_type, pk )
);
create table MY_CHILD_TABLE_CALENDAR_ALERT (
PK number(19) primary key,
tab_type varchar2(10) not null
check ( tab_type = 'CALENDAR' ),
CALENDAR_ID number(19),
APPOINTMENT date,
foreign key (PK) references MY_PARENT_TABLE (PK)
on delete cascade,
foreign key (PK, tab_type) references MY_PARENT_TABLE (PK, tab_type)
on delete cascade,
unique ( tab_type, pk )
);
alter table MY_PARENT_TABLE
add (
doc_vc as ( case when tab_type = 'DOCUMENT' then pk end ),
cal_vc as ( case when tab_type = 'CALENDAR' then pk end ),
constraint doc_fk
foreign key ( doc_vc )
references MY_CHILD_TABLE_DOCUMENT_ATTACHMENT ( pk )
on delete cascade
deferrable initially deferred,
constraint cal_fk
foreign key ( cal_vc )
references MY_CHILD_TABLE_CALENDAR_ALERT ( pk )
on delete cascade
deferrable initially deferred
);
insert into MY_PARENT_TABLE ( pk, tab_type, customer_id, create_datetime )
values ( 1, 'DOCUMENT', 1, sysdate );
insert into MY_CHILD_TABLE_DOCUMENT_ATTACHMENT
values ( 1, 'DOCUMENT', 1 );
insert into MY_PARENT_TABLE ( pk, tab_type, customer_id, create_datetime )
values ( 2, 'CALENDAR', 1, sysdate );
insert into MY_CHILD_TABLE_CALENDAR_ALERT
values ( 2, 'CALENDAR', 1, sysdate );
commit;
select count(*) from my_parent_table;
COUNT(*)
2
delete MY_CHILD_TABLE_DOCUMENT_ATTACHMENT;
select count(*) from my_parent_table;
COUNT(*)
1
delete MY_CHILD_TABLE_CALENDAR_ALERT;
select count(*) from my_parent_table;
COUNT(*)
0 As a side bonus, you've now also got constraints enforcing the exclusive arc relationship. I.e. that each parent row has an entry in only one child table.
You can read more about enforcing the exclusion relationship at:
https://blogs.oracle.com/sql/implementing-a-mutually-exclusive-relationship-in-the-database https://blogs.oracle.com/sql/protecting-peoples-privacy%3a-implementing-cross-table-constraints-part-ii