Questions tagged [primary-key]

A key is a set of attributes that is irreducibly unique and non-nullable within a table. Irreducible means that all the attributes of the key are necessary to guarantee uniqueness - remove any one attribute and the uniqueness property would be lost. A key may consist of zero, one or more attributes and a relational table (relation variable) must have at least one key and may have more than one.

When a table has more than one key then by convention one of the keys is designated the primary one (meaning it is the preferred identifier or has some other significance for the database designer or user). The others are called secondary keys or alternate keys.

In relational database design the primary key is frequently the one referenced by foreign keys in other tables. That's not always the case however. In principle a primary key is no different in features or function from a non-primary key. The primary-key tag is therefore appropriate for any discussion about keys in general.

Keys are more formally known as candidate keys or minimal superkeys.

Attributes of a key are called prime attributes. Attributes which are not part of any key are called non-prime.

In SQL, keys are optional on tables. Primary keys are defined in the ISO SQL Standard, through the PRIMARY KEY constraint. The syntax to add such a constraint to an existing table is defined in SQL:2003 like this:

ALTER TABLE <TABLE identifier> 
      ADD [ CONSTRAINT <CONSTRAINT identifier> ] 
      PRIMARY KEY ( <COLUMN expression> {, <COLUMN expression>}... )

The primary key can also be specified directly during table creation. In the SQL Standard, primary keys may consist of one or multiple columns. Each column participating in the primary key is implicitly defined as NOT NULL. Note that some DBMS require explicitly marking primary-key columns as NOT NULL.

CREATE TABLE TABLE_NAME (
    id_col  INT,
    col2    CHARACTER VARYING(20),
    ...
    CONSTRAINT tab_pk PRIMARY KEY(id_col),
    ...
)

If the primary key consists only of a single column, the column can be marked as such using the following syntax:

CREATE TABLE TABLE_NAME (
    id_col  INT  PRIMARY KEY,
    col2    CHARACTER VARYING(20),
    ...
)