Setting up Primary Keys
You can create primary keys using SQL statements or PSQL Control Center. See
Columns Tasks
in
PSQL User's Guide
.
Creating a Primary Key During Table Creation
You can create a primary key when you create a table, by using the PRIMARY KEY keywords in your CREATE TABLE statement. A primary key can consist of one or more columns. The following example shows the column named
id
being created then being designated the primary key:
CREATE TABLE mytable (id INTEGER,
myname CHAR(20),
PRIMARY KEY(id))
The next example shows how to create a primary key using more than one column as the unique key value:
CREATE TABLE mytable (id INTEGER,
myname CHAR(20),
PRIMARY KEY(id, myname))
Regardless of whether you specify the UNIQUE attribute on the column or columns that you designate as a primary key, the database engine automatically creates an index on the designated columns that does not allow duplicate values or null values in the columns. Null values are never allowed in a key column. Every primary key value must be unique.
For more examples, see CREATE TABLE in
SQL Engine Reference
.
Adding a Primary Key to an Existing Table
You can add a primary key to an existing table through PCC or by using the ALTER TABLE statement with ADD PRIMARY KEY. In
PSQL User's Guide
, see
To set or remove a column as a primary key
and
SQL Editor
.
You must create the primary key on a column or columns that do not allow duplicate values or null values.
If necessary, you can modify the column attributes and make the column the primary key at the same time. Here is an example using SQL:
ALTER TABLE mytable MODIFY id INTEGER UNIQUE NOT NULL PRIMARY KEY
If you want to add a primary key consisting of more than one column, you must add the key separately:
ALTER TABLE mytable ADD PRIMARY KEY(id, myname)
For more examples, see
ALTER TABLE
in
SQL Engine Reference
.