Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Two primary key constraints in a table with two different constraint name

anishjpMar 18 2015 — edited Mar 19 2015

Hi guys

I am trying to create the below table

Table name: TITLE_COPY

Column nameCopy_idTitle_idStatus

Key Type

PKPK, FK
Null/UniqueNN, UNN, UNN
CheckAvailable, Destroyed, Rented, Reserved
FK Ref TableTitle
FK Ref ColTitle_id
Data TypeNumberNumberVarchar2
Length101015

The below SQL statement can create it

CREATE TABLE title_copy (copy_id NUMBER(10),

                                          title_id NUMBER(10) CONSTRAINT title_copy_title_id_fk REFERENCES title(title_id),

                                          constraint title_copy_title_id_pk primary key(title_id, copy_id),

                                          status VARCHAR2(15) NOT NULL CONSTRAINT status_ck CHECK (status IN ('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED'))

                                         );

Two primary keys (title_id, copy_id) are created with the same constraint name (title_copy_title_id_pk) (-> table level constraint). What if I want to have two different constraint names for the primary keys, say title_copy_title_id_pk for column title_id and title_copy_copy_id_pk for column copy_id?

Can I achieve this with a column level constraint? Something like below (complains about the extra primary key)

CREATE TABLE title_copy (copy_id NUMBER(10) CONSTRAINT title_copy_copy_id_pk PRIMARY KEY,

                                          constraint title_copy_title_id_pk primary key(title_id),

                                          title_id NUMBER(10) CONSTRAINT title_copy_title_id_fk REFERENCES title(title_id),

                                          status VARCHAR2(15) NOT NULL CONSTRAINT status_ck CHECK (status IN ('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED'))

                                         );

in summary, can I define two primary keys with different constraint name in a column level constraint?

This post has been answered by John Stegeman on Mar 18 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 16 2015
Added on Mar 18 2015
8 comments
4,256 views