CODEWITHSUNDEEP
sqldatabaseoracle-databaseprimary-key
Jon
Jon

Reputation: 43

SQL - 2 Primary Keys in 1 Table

I have been researching this issue for several weeks now and despite the resources on this site and others, I cannot find any proper examples to lead me in the right direction. For an assignment, I am trying to create a table in SQL containing two PKs. I now understand that this isn't possible, but I cannot seem to understand why, mainly because it has been required of me. I attempted to change my code and separate the keys but I was told that was incorrect and to stick with the two PKs. Any assistance or direction would be greatly appreciated. Here is my current code for the table:

SQL> CREATE TABLE solds_ss /* This is creating the SOLDS table from the instance charts */
2      (
3           invoice_no     NUMBER(7)
4              CONSTRAINT invoice_no_pk PRIMARY KEY,
5              CONSTRAINT invoice_no_fk
6                 FOREIGN KEY (invoice_no)
7                 REFERENCES invoices_ss (invoice_no),
8           item_no     NUMBER(6)
9           CONSTRAINT item_no_pk PRIMARY KEY,
10             CONSTRAINT item_no_nn NOT NULL,
11             CONSTRAINT item_no_fk
12                FOREIGN KEY (item_no)
13                REFERENCES items_ss (item_no),
14          item_qty_sold     NUMBER(3)
15             CONSTRAINT item_qty_sold_nn NOT NULL,
16          item_price     NUMBER(6,2)
17             CONSTRAINT item_price_nn NOT NULL,
18          item_tracking_no     VARCHAR2(30)
19             CONSTRAINT item_tracking_no_nn NOT NULL
20     );
     CONSTRAINT item_no_pk PRIMARY KEY,
     *
ERROR at line 9:
ORA-02260: table can have only one primary key

Upvotes: 0

Views: 9186

Answers (4)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

A primary key has three properties:

  1. It is not nullable.
  2. It is unique.
  3. It never changes.

So although Oracle will only allow you to define one primary key constraint per table, you can easily make a second, third, or fourth column with all the properties of a primary key by:

  1. Give a NOT NULL constraint to the column(s).
  2. Create a UNIQUE constraint on the column(s).
  3. Swear up and down on a stack of Hershey bar wrappers (chocolate is sacred! :-) that you will never, ever, EVER change the value of any of the columns in your constraint. If you want this to be enforced you can do so using a trigger.

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

Following up on my comment, I suspect that what you want is a composite primary key - two columns that together make up the primary key/uniquely identify a row in the table. This is very different to "having two primary keys" - the value of each column might be repeated, but the combination of column 1 and column 2 must be unique:

InvoiceNo ItemNo
Inv1      Itm1
Inv1      Itm2
Inv2      Itm1
Inv2      Itm2

The table data above wouldn't be allowed if InvoiceNo was a primary key, or ItemNo was a primary key (each column contains repeated values when viewed in isolation), but is allowed if the primary key is defined as InvoiceNo and ItemNo in combination

Hence I think the sql you're looking for is more like:

 CREATE TABLE solds_ss /* This is creating the SOLDS table from the instance charts */
 (
      invoice_no     NUMBER(7) NOT NULL,
      item_no     NUMBER(6) NOT NULL,
      item_qty_sold     NUMBER(3) NOT NULL,
      item_price     NUMBER(6,2) NOT NULL,
      item_tracking_no     VARCHAR2(30) NOT NULL,

         CONSTRAINT invoice_no_fk
            FOREIGN KEY (invoice_no)
            REFERENCES invoices_ss (invoice_no),
         CONSTRAINT item_no_fk
            FOREIGN KEY (item_no)
            REFERENCES items_ss (item_no),

         --composite pk
         CONSTRAINT solds_ss_pk PRIMARY KEY (invoice_no, item_no)

 );

Upvotes: 4

npc
npc

Reputation: 47

Primary key constraint designates a column as the primary key of a table or view. A composite primary key designates a combination of columns as the primary key.

Oracle doesn't allow multiple primary keys on a table or view. Look for Primary key constraints https://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You cannot create two primary keys. Primary keys have three properties:

  • They are unique.
  • They are non-NULL.
  • There is only one per table.

It is the third property that prevents you from having two of them. You can declare one column or groups of columns as both unique and not null. But only one such set of keys can be a primary key.

Upvotes: 0

Related Questions