Managing primary keys (SQL)

You can create and alter the primary key in Interactive SQL using the CREATE TABLE and ALTER TABLE statements. These statements let you set many table attributes, including column constraints and checks.

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

To add a primary key (SQL)

  1. Connect to the database as a user with DBA authority.

  2. Execute a ALTER TABLE statement for the table on which you want to configure the primary key.

To modify a primary key (SQL)

  1. Connect to the database as a user with DBA authority.

  2. Execute an ALTER TABLE statement to drop the existing primary key.

  3. Execute an ALTER TABLE statement to add a primary key.

To delete a primary key (SQL)

  1. Connect to the database as a user with DBA authority.

  2. Execute an ALTER TABLE statement using the DELETE PRIMARY KEY clause.

Example 1

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 );

See ALTER TABLE statement, and Managing primary keys (Sybase Central).