PRIMARY KEY
Remarks
Include PRIMARY KEY in the ADD clause to add a primary key to a table definition. The primary key is a unique index that does not include null values. When you specify a primary key, PSQL creates a unique index with the specified attributes on the defined group of columns.
Because a table can have only one primary key, you cannot add a primary key to a table that already has a primary key defined. To change the primary key of a table, delete the existing key using a DROP clause in an ALTER TABLE statement and add the new primary key.
Note You must be logged in to the database using a database name before you can add a primary key or conduct any other referential integrity (RI) operation.
Include PRIMARY KEY in the ADD clause with the ALTER TABLE statement to add a primary key to a table definition.
Before adding the primary key, you must ensure that the columns in the primary key column list are defined as NOT NULL. A primary key is a unique index and can be created only on not nullable columns.
If a unique index on not nullable columns already exists, the ADD PRIMARY KEY does not create another unique index. Instead, the existing unique index is promoted to a primary key. For example, the following statements would promote the named index T1_C1C2 to be a primary key.
CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(10) NOT NULL)
CREATE UNIQUE INDEX t1_c1c2 ON t1(c1,c2)
ALTER TABLE t1 ADD PRIMARY KEY(c1, c2)
If such a primary key is dropped, the primary key would be switched to a unique index.
ALTER TABLE t1 DROP PRIMARY KEY
If no unique index on not nullable columns exists in the table, ADD PRIMARY KEY creates a unique index on not nullable columns. DROP PRIMARY KEY completely deletes the unique index.
Include a PRIMARY KEY clause with the CREATE TABLE statement to add a primary key to a table definition.
To define referential constraints on your database, you must include a PRIMARY KEY clause to specify the primary key on the parent table. The primary key can consist of one column or multiple columns but can only be defined on columns that are not null. The columns you specify must also appear in the column Definitions list of the CREATE TABLE statement.
When you specify a primary key, PSQL creates an index with the specified attributes on the defined group of columns. If the columns are not specifically defined as NOT NULL in the CREATE TABLE statement, PSQL forces the columns to be not nullable. PSQL also creates a unique index on the columns.
For example, the following two statements yield the same results:
CREATE TABLE t1 (c1 INT, c2 CHAR(10), PRIMARY KEY(c1,c2))
CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(10) NOT NULL, PRIMARY KEY(c1,c2))
Examples
The following statement defines a primary key on a table called Faculty.
ALTER TABLE Faculty ADD PRIMARY KEY (ID)
The ID column is defined as a unique index that does not include null values.
See Also