Re: LONG-running DELETE -- please help

"Linkage" tables normally do have primary keys. If tables A and B have a many-to-many relationship, then your linkage table C stores the valid combinations of A and B. C has a foreign key to A and a foreign key to B. These foreign key columns in C are the primary key of C, and that is what prevents you from having multiple entries in C for a single valid combination of A and B. You have a unique constraint, but a primary key would be better, since you don't want rows in the intersection table where either employeeid or initiativeid is null.

"dib42700" <dib42700_at_glaxowellcome.com> wrote in message news:01c28db5$3ab40af0$6601a8c0_at_us0097739...
> The following Oracle DELETE statement is EXTREMENTLY long-running:
>
> delete from tmmAdministratorInitiative
> where InitiativeID = 1
> and employeeid = '00701280K'
>
> Here's the DDL for the table:
>
> CREATE TABLE TMMADMINISTRATORINITIATIVE (
> EMPLOYEEID VARCHAR2(20) NOT NULL
> ,INITIATIVEID NUMBER(10) NOT NULL
> ,CRTDT DATE
> ,CRTUSR VARCHAR2(50)
> ,MODDT DATE
> ,MODUSR VARCHAR2(50)
> ,CONSTRAINT U_ADMIN_INITIATIVE UNIQUE ( EMPLOYEEID, INITIATIVEID )
> ,CONSTRAINT FK_EMPLOYEEID_2 FOREIGN KEY ( EMPLOYEEID ) REFERENCES
> TBLEMPLOYEE ( EMPLOYEEID )
> ,CONSTRAINT FK_INITIATIVEID_2 FOREIGN KEY ( INITIATIVEID ) REFERENCES
> TBLINITIATIVE ( INITIATIVEID ) ON DELETE CASCADE
> );
>
> There's no primary key because this is a"linkage" table in a many-to-many
> relationship.
>
> There are just two rows in the table!
>
> Any help understanding why the DELETE statement takes so long would be
> greatly appreciated!
>
Received on Sat Nov 16 2002 - 16:45:53 CST