CREATE TABLE foo ( id1 NUMBER, id2 NUMBER, name VARCHAR2(20));
In this table, I desire to have the ID1 and ID2 columns taken together to form the composite primary key. I can add that PK constraint to the table as follows:
ALTER TABLE foo ADD CONSTRAINT foo_pk PRIMARY KEY (id1,id2);
Many people are used to creating primary keys in line with the column when you create the table similar to the following:
CREATE TABLE foo ( id1 NUMBER PRIMARY KEY, name VARCHAR2(20));
When defining the PK constraint in line with the column definition, you cannot define a composite primary key. The only way to define a composite primary key is out of line with the column. You can still do this in the CREATE TABLE statement, but it will be an out-of-line constraint similar to the following:
CREATE TABLE foo ( id1 NUMBER, id2 NUMBER, name VARCHAR2(20)) CONSTRAINT foo_pk PRIMARY KEY (id1,id2);
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.