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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top