CODEWITHSUNDEEP
databaseoracle-databaseforeign-keyssql-delete
user2210179
user2210179

Reputation: 73

Oracle delete data which would violate a to-be added foreign key

I am attempting to clean up some bad data from a DEV database (everyone has the ability to insert data for self testing) and add RI through foreign key constraints. The tables are set up for the FKs, however, the data is not.

Is there a relatively simple way to delete data from a (tobe) child table which would violate the composite keys?

CREATE TABLE parentTable
    ( key1    VARCHAR2(1) NOT NULL
    , key2    VARCHAR2(1) NOT NULL
     );

CREATE TABLE childTable
    ( key1    VARCHAR2(1) NOT NULL
    , key2    VARCHAR2(1) NOT NULL
    , key3    VARCHAR2(1) NOT NULL
     );

ALTER TABLE parentTable ADD CONSTRAINT pk_parentTable primary key(key1,key2);
ALTER TABLE childTable ADD CONSTRAINT pk_childTable primary key(key1,key2,key3);

insert into parentTable values('0','A');
insert into parentTable values('0','B');
insert into parentTable values('1','C');

insert into childTable values('0','A','X');
insert into childTable values('0','B','Y');
insert into childTable values('1','C','X');
insert into childTable values('0','C','X');   -- bad data

The query I've been using to identify the bad data is as follows.

select distinct key1, key2 from childTable
  minus 
  select distinct key1, key2 from parentTable;

How can I take this result set and delete them from child table. Logically I've attempted to use the following query below, but it gives an error of ORA-00928: missing SELECT.

with x as(
select distinct key1, key2 from childTable
minus 
select distinct key1, key2 from parentTable
  )
delete from childtable where key1 = x.key1 AND key2 = x.key2;

Is there another way to easily delete this data?

Upvotes: 1

Views: 823

Answers (3)

Marco Baldelli
Marco Baldelli

Reputation: 3728

A possible solution is:

delete
  from childTable
 where not exists (select null
                     from parentTable
                    where childTable.key1 = parentTable.key1
                      and childTable.key2 = parentTable.key2);

Upvotes: 0

Rene
Rene

Reputation: 10541

Alternatively:

delete from childtable 
where (key1,key2) not in 
    (select key1,key2 from parenttable)

Upvotes: 0

Mark J. Bobak
Mark J. Bobak

Reputation: 14393

Oracle actually has some built-in functionality to handle this.

First, run @?/rdbms/admin/utlexcpt.sql to create the exceptions table.

Then, (attempt to) add the FK constraint, like this:

alter table childtable add constraint my_fk foreign key (key1,key2) references parenttable(key1,key2) exceptions into exceptions;

That will either complete, or it will error out. If it errors out, the row ids of the rows that violate the constraint will be in the exceptions table.

Then, it's a simple matter of executing:

delete from childtable where rowid in(select row_id from exceptions);

Then, just re-execute the alter table add constraint above.

More details can be found here: http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52228

Hope that helps....

Upvotes: 1

Related Questions