Creating and modifying a primary key (SQL)

You can configure a primary key for a table to help improve query performance on the table.

Prerequisites

You must have DBA authority, owner of the table, or have permissions to modify the table.

Columns in the primary key cannot contain NULL values. You must specify NOT NULL on columns in the primary key.

Context and remarks

Many.

Add a primary key using SQL

Connect to the database.
To add a primary key, execute an ALTER TABLE...ALTER column-name column-alteration statement.
To delete a primary key, execute an ALTER TABLE...DROP PRIMARY KEY statement.
To modify a primary key, execute an ALTER TABLE...DROP PRIMARY KEY statement to drop the existing primary key, and then execute an ALTER TABLE...ALTER column-name column-alteration statement to set the new primary key for the table.

Results

A primary key definition is added to the table definition in the database.

Next

None.

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.

If you want to change the primary key to use 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 );

Creating and modifying a primary key (SQL)

Add a primary key using SQL

See also