Re: deleting primary keys???

Quite right. You are not allowed to create orphans. So you will be prevented from deleting records in the parent table if there are records in the child table (the one with the foreign key) which relate to it. You'll get an ORA-02292: integrity constraint (SCOTT.EDFK) violated - child record found if you try it.

Of course, you can delete any parent records that have no children (though when you do so, be aware of horrible locking issues -which you can read about on my site [see the Tips page, and then the Basic Administration stuff. There's a section on constraints there that describes the problem]). So yes, one fix is to go to the child table and do manual deletes of all records that relate to the parent record you really want to delete.

However, there is a workaround: you can create the foreign key constraint with the 'ON DELETE CASCADE' keywords. The syntax (assuming two tables called e1 and d1) runs like this:

alter table e1 add (
constraint edfk foreign key(deptno)
references d1(deptno)
on delete cascade);

If you now delete a parent record that has children, you simply get:

SQL> delete from d1 where deptno=10;

1 row deleted.

Which is a complete lie, of course... because what it neglects to tell you is that it's gone off and deleted three records in the child table, too! (A count(*) from e1 -which is just a copy of ye olde standard emp table- after this reveals a mere 11 records, not the 14 originals). Personally, I dislike automatic -and totally silent- deletes of child records (and without even an 'are you sure?' prompt!) so much, I'd never use the 'on delete' clause in a million years. But your mileage might vary!

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"AJG" <algroot_at_rogers.com> wrote in message
news:LGUd8.4002$qQ.879_at_news2.bloor.is...


> Hi,

>

> I'm new at the Oracle game so forgive me if this is a stupid question.  I

> have created tables and assigned primary and foreign key constraints,

> populated the data with using the INSERT INTO command and updated several

> records (i.e. UPDATE slsrep SET rate=.06 WHERE slsrnumb=3; etc...).

>

>  I am now required to delete 1 record from each table, but as I understand

> it , if I delete a row that contains a primary key that is used as a

foreign


> key in another table I will get an integrity constraint error.  So, if I

> delete the record in the table that contains the foreign key first and

then


> the record in the table where it is a primary key, will this solve my

> problem, if it is indeed a problem? Is there a better way?  Any help is

> greatly appreciated.  Thanks in advance.

>

>