Hi guys
I am trying to create the below table
Table name: TITLE_COPY
| Column name | Copy_id | Title_id | Status |
|---|
Key Type | PK | PK, FK | |
| Null/Unique | NN, U | NN, U | NN |
| Check | | | Available, Destroyed, Rented, Reserved |
| FK Ref Table | | Title | |
| FK Ref Col | | Title_id | |
| Data Type | Number | Number | Varchar2 |
| Length | 10 | 10 | 15 |
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?