Q

Installing a composite primary key on a column without unique data

I was wondering if I could install a composite primary key on a table which does not have unique data on one of the columns. When I try to install a composite primary key using that column, obviously it blows up. Is there a workaround other than using a view?

I was wondering if I could install a composite primary key on a table which does not have unique data on one of the columns. When I try to install a composite primary key using that column, obviously it blows up. Is there a workaround other than using a view?
You can have composite primary keys on a table. First, let's create a table:

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);
This was last published in August 2005

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close