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

Managing foreign keys (SQL)

You can create and modify the foreign 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.

A table can only have one primary key defined, but it may have as many foreign keys as necessary.

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

1. Connect to the database with DBA authority.

2. Execute a ALTER TABLE statement.

Example 1 

You can create a table named emp_skill, which holds a description of each employee's skill level for each skill in which they are qualified, as follows:

CREATE TABLE emp_skill(
   emp_id INTEGER NOT NULL,
   skill_id INTEGER NOT NULL,
   "skill level" INTEGER NOT NULL,
   PRIMARY KEY( emp_id, skill_id ),
   FOREIGN KEY REFERENCES employee,
   FOREIGN KEY REFERENCES skill
)

The emp_skill table definition has a primary key that consists of two columns: the emp_id column and the skill_id column. An employee may have more than one skill, and so appear in several rows, and several employees may possess a given skill, so that the skill_id may appear several times. However, there may be no more than one entry for a given employee and skill combination.

The emp_skill table also has two foreign keys. The foreign key entries indicate that the emp_id column must contain a valid employee number from the employee table, and that the skill_id must contain a valid entry from the skill table.

Example 2 

You can add a foreign key called foreignkey to the existing table skill and reference this foreign key to the primary key in the table contact, as follows:

ALTER TABLE skill
ADD FOREIGN KEY "foreignkey" ("skill_id")
REFERENCES "DBA"."contact" ("id")

This example creates a relationship between the skill_id column of the table skill (the foreign table) and the id column of the table contact (the primary table). The "DBA" signifies the owner of the table contact.

Example 3 

You can specify properties for the foreign key as you create it. For example, the following statement creates the same foreign key as in Example 2, but it defines the foreign key as NOT NULL along with restrictions for when you update or delete.

ALTER TABLE skill
ADD NOT NULL FOREIGN KEY "foreignkey" ("skill_id")
REFERENCES "DBA"."contact" ("id")
ON UPDATE RESTRICT
ON DELETE RESTRICT

In Sybase Central, you can also specify properties in the Foreign Key Creation wizard or on the foreign key's property sheet.

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