Re: problem with foreign key

"Rüdiger J. Schulz" wrote:
>
> hi all,
>
> I have two tables:
>
> CREATE TABLE PARENT (
> ID NUMBER NOT NULL,
> NAME VARCHAR2(100),
> CONSTRAINT PK_PARENT PRIMARY KEY(ID)
> ) ;
>
> CREATE TABLE CHILD (
> ID NUMBER NOT NULL,
> DT DATE DEFAULT sysdate NOT NULL,
> PID NUMBER NOT NULL,
> CONSTRAINT PK_CHILD PRIMARY KEY(ID),
> CONSTRAINT FK_CHILD FOREIGN KEY(PID)
> REFERENCES PARENT(ID) ON DELETE CASCADE);
>
> transaction 1: delete a row in the parent table *without* a commit
> transaction 2: wants to insert a row in the CHILD-table
>
> *problem*: transaction 2 wait until transaction 1 send a commit!!!
>
> (rem: I have tested it without ON DELETE CASCADE and with ON DELETE SET NULL.)
>
> is it true, that oracle lock the whole child-table, while deleting a parent-
> key?
> or can I do something else?
>
> thanx in advance
> Rüdiger
>
> (eMail: rjs_at_berlin.de)
>
> --
> __________________________________________________________
> News suchen, lesen, schreiben mit http://newsgroups.web.de
You **must** implement FKs with an index or trhe RDBMS kernel will lock the child table with an exclusive lock when deleting a row from the parent. This is a well documented "feature", and there are reasons for it (also well documented).

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.