Managing primary keys (SQL)

You can manage primary keys by using SQL to help improve query performance on a table.

Prerequisites

You must be the owner of the table, or have one of the following privileges:

ALTER privilege on the table
ALTER ANY TABLE system privilege
ALTER ANY OBJECT system privilege

Columns in the primary key cannot contain NULL values.

Task

Connect to the database.

Option	Action
Create a primary key	Execute an ALTER TABLE table-name ADD PRIMARY KEY (column-name) statement.
Delete a primary key	Execute an ALTER TABLE table-name DROP PRIMARY KEY statement.
Alter a primary key	Drop the existing primary key before creating a new primary key for the table.

Results

A primary key is added, deleted, or altered.

Example

The following statement creates a table named Skills and assigns the SkillID column as the primary key:

CREATE TABLE Skills (
   SkillID INTEGER NOT NULL,
   SkillName CHAR( 20 ) NOT NULL,
   SkillType CHAR( 20 ) NOT NULL,
   PRIMARY KEY( SkillID )
);

The primary key values must be unique for each row in the table, which in this case means that you cannot have more than one row with a given SkillID. Each row in a table is uniquely identified by its primary key.

To change the primary key to use the SkillID and SkillName columns together for the primary key, you must first delete the primary key that you created, and then add the new primary key:

ALTER TABLE Skills DELETE PRIMARY KEY;
ALTER TABLE Skills ADD PRIMARY KEY ( SkillID, SkillName );

Managing primary keys (SQL)

Task

See also