Re: Setting up constraints

On 16 May 2002 10:44:23 -0700, aaron_rouse_at_yahoo.com (Aaron Rouse) wrote:

>I am trying to setup some contraints in Oracle 8i and for some reason
>I am getting told by Oracle that ID is not a primary key and it errors
>out on that. However in the attached SQL, I set the column ID as a PK
>up top along with PQUESTIONID. Where might my error lie in this SQL?
>It errors out before it gets to the very last constraint, so erroring
>out on the second to last constraint.
>
>=======================================================
>--Remove QDEPNDENCY PK
>ALTER TABLE QDEPENDENCY DROP CONSTRAINT PK_QDEPENDENCY;
>--Add new QDEPENCY PK
>ALTER TABLE QDEPENDENCY ADD CONSTRAINT PK_QDEPENDENCY PRIMARY KEY (ID,
>PQUESTIONID);
>--Add QDEPENDLISTS PK
>ALTER TABLE QDEPENDLISTS ADD CONSTRAINT PK_QDEPENDLISTS PRIMARY KEY
>(QDEPENDID, LISTITEMID);
>-- Add constraint for deletion of Dependents
>ALTER TABLE COILSAFE.QDEPENDLISTS
> ADD CONSTRAINT REM_QDEPENDLISTS
> FOREIGN KEY (QDEPENDID)
> REFERENCES COILSAFE.QDEPENDENCY (ID)
> ON DELETE CASCADE;
>-- Add constraint for deletion of Questions
>ALTER TABLE COILSAFE.QDEPENDENCY
> ADD CONSTRAINT REM_QDEPENDENCY
> FOREIGN KEY (PQUESTIONID)
> REFERENCES COILSAFE.FRMQUESTIONS (ID)
> ON DELETE CASCADE;
The PK of QDEPENDENCY is
(ID,PQUESTIONID)
so QDEPENDLISTS may not reference only (ID)

A one-column-FK may nor reference a two-column-PK.

hth
regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Thu May 16 2002 - 13:03:00 CDT