Ask the Expert

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?

    Requires Free Membership to 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 first published in August 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: