Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

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 DBA user.

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 DBA user.

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 DBA user.

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

Example 1

The following statement creates the same Skills table as before, except that it adds a primary key using the values in the SkillID column:

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

For more information, see ALTER TABLE statement, and Managing primary keys (Sybase Central).