Can a Microsoft SQL table have more than one Primary Key?

Question

I was always curious but can't find a concise answer. Any help?

Answer 1

As noted by MJB, a table can have at most one Primary Key, and should always have one (never zero). A table may, however, have multiple candidate keys - one of which is designated as the primary key. In normalization theory, the primary key is not critical; the candidate keys (the primary key is one of the candidate keys) are crucial in the theorems related to normalization.

An example of a table with three possible unique keys is, appropriately enough, the Table of Elements - where the Atomic Number, Symbol or Element Name could all be used as the primary key. Indeed, depending on whether you're doing chemistry or physics, the atomic number or symbol might be the most appropriate primary key, and the other would be an alternative key (and the element name would probably always be an alternative key rather than the primary key):

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL UNIQUE CONSTRAINT c1_elements
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE CONSTRAINT c2_elements,
    name            CHAR(20) NOT NULL UNIQUE CONSTRAINT c3_elements,
    atomic_weight   DECIMAL(8,4) NOT NULL,
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

(The notation is for Informix Dynamic Server; the differences between that and Microsoft SQL Server or any other SQL DBMS are pretty trivial. You can see that I've not designated any of the possible keys as 'the primary key'. If I did, I'd probably nominate Atomic_Number as the primary key.)

---

Thanks for the pointers, Thomas. I found this in the ISO/IEC 9075-2:2003 (SQL/Foundation):

§11.7 <unique constraint definition>

Function

Specify a uniqueness constraint for a table.

Format

<unique constraint definition> ::=
      <unique specification> <left paren> <unique column list> <right paren>
    | UNIQUE ( VALUE )
<unique specification> ::=
      UNIQUE
    | PRIMARY KEY
<unique column list> ::= <column name list>

Syntax Rules

1. Each column identified by a <column name> in the <unique column list> is an operand of a grouping operation. The Syntax Rules of Subclause 9.10, “Grouping operations”, apply.
2. Let T be the table identified by the containing <table definition> or <alter table statement>. Let TN be the <table name> of T.

3. If <unique column list> UCL is specified, then

   a. Each <column name> in the <unique column list> shall identify a column of T, and the same column shall not be identified more than once.
   b. The set of columns in the <unique column list> shall be distinct from the unique columns of any other unique constraint descriptor that is included in the base table descriptor of T.
   c. Case:
   i. If the <unique specification> specifies PRIMARY KEY, then let SC be the <search condition>:

           UNIQUE ( SELECT UCL FROM TN )
           AND
           ( UCL ) IS NOT NULL
   

   ii. Otherwise, let SC be the <search condition>:

           UNIQUE ( SELECT UCL FROM TN )
   

4. If UNIQUE (VALUE) is specified, then let SC be the <search condition>:

       UNIQUE ( SELECT TN.* FROM TN )
   

5. If the <unique specification> specifies PRIMARY KEY, then for each <column name> in the explicit or implicit <unique column list> for which NOT NULL is not specified, NOT NULL is implicit in the <column definition>.

6. A <table definition> shall specify at most one implicit or explicit <unique constraint definition> that specifies PRIMARY KEY.

7. If a <unique constraint definition> that specifies PRIMARY KEY is contained in an <add table constraint definition>, then the table identified by the <table name> immediately contained in the containing <alter table statement> shall not have a unique constraint that was defined by a <unique constraint definition> that specified PRIMARY KEY.

Access Rules

None.

General Rules

1. A <unique constraint definition> defines a unique constraint.
   NOTE 254 — Subclause 10.8, “<constraint name definition> and <constraint characteristics>”, specifies when a constraint is effectively checked.

2. The unique constraint is not satisfied if and only if

     EXISTS ( SELECT * FROM TN WHERE NOT ( SC ) )
   

   is True.

Conformance Rules

1. Without Feature S291, “Unique constraint on entire row”, conforming SQL language shall not contain UNIQUE(VALUE).
2. Without Feature T591, “UNIQUE constraints of possibly null columns”, in conforming SQL language, if UNIQUE is specified, then the <column definition> for each column whose <column name> is contained in the <unique column list> shall contain NOT NULL.

NOTE 255 — The Conformance Rules of Subclause 9.10, “Grouping operations”, also apply.

---

Welcome to the wonderful world of the SQL standard! It seems that feature T591 allows for possibly null values in the columns of a UNIQUE constraint (but not in the columns of a PRIMARY KEY). The net result is that you have to understand how the following query works when there are NULLs in any of the columns in the UCL (unique column list):

      EXISTS ( SELECT * FROM TN WHERE NOT (
               UNIQUE ( SELECT UCL FROM TN ) ) )

And I know I am not sure how the UNIQUE operation works in those circumstances.

---

§8.10 <unique predicate>

Function

Specify a test for the absence of duplicate rows.

Format

<unique predicate> ::= UNIQUE <table subquery>

Syntax Rules

1. Each column of user-defined type in the result of the <table subquery> shall have a comparison type.
2. Each column of the <table subquery> is an operand of a grouping operation. The Syntax Rules of Subclause 9.10, “Grouping operations”, apply.

Access Rules

None.

General Rules

1. Let T be the result of the .
2. If there are no two rows in T such that the value of each column in one row is non-null and is not distinct from the value of the corresponding column in the other row, then the result of the <unique predicate> is True; otherwise, the result of the is False.

Conformance Rules

1) Without Feature F291, “UNIQUE predicate”, conforming SQL language shall not contain a <unique predicate>.

NOTE 193 — The Conformance Rules of Subclause 9.10, “Grouping operations”, also apply.

Answer 2

No, a table can't have more than one PK.

It can however have a composite key (one PK that contains more than one column in its definition). Also, you can have multiple unique constraints which also can span multiple columns.

Answer 3

As far as I know, only one. However you can have more than one column (usually up to 8 or 20, depending on the RDBMS) combined with others as a composite key to uniquely identify the row. The lone primary key, single-column or composite, can also be used for joining to a foreign key in another table (one-to-many, many-to-one or many-to-many relationships).

Answer 4

It cannot have more than one primary key. It can, however, have more than one column in the primary key. It can also have more than one unique index. Generally, the unique index is the primary key, but it is possible to have multiple unique indices on a single table. Off the top of my head I cannot think of an example, but when I do I will add it.

EDIT: Maybe this: In the US, the Department of Motor Vehicles might have a person table with 2 unique columns -- Social Security Number and Driver's License Number. Both should be unique.