ASA SQL User's Guide
  Working with Database Objects
    Working with tables
      Managing primary keys

Managing primary keys (SQL)

You can create and modify 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 the column in the primary key.

To modify the primary key of an existing table (SQL)

1. Connect to the database with DBA authority.

2. Execute a ALTER TABLE statement.

Example 1 

The following statement creates the same skill table as before, except that it adds a primary key:

CREATE TABLE skill (
  skill_id INTEGER NOT NULL,
  skill_name CHAR( 20 ) NOT NULL,
  skill_type CHAR( 20 ) NOT NULL,
  primary key( skill_id )
)

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 skill_id. Each row in a table is uniquely identified by its primary key.

Example 2 

The following statement adds the columns skill_id and skill_type to the primary key for the skill table:

ALTER TABLE skill (
  ADD PRIMARY KEY ( "skill_id", "skill_type" )
)

If a PRIMARY KEY clause is specified in an ALTER TABLE statement, the table must not already have a primary key that was created by the CREATE TABLE statement or another ALTER TABLE statement.

Example 3 

The following statement removes all columns from the primary key for the skill table. Before you delete a primary key, make sure you are aware of the consequences in your database.

ALTER TABLE skill
  DELETE PRIMARY KEY

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