Sybase 12.4.2 manual 126

Working with tables

Creating unenforced foreign keys

Creating an unenforced foreign key in Sybase Central

For more information, see the Sybase Central online Help.

Note Multi-column primary keys are not enforced, and require the keyword UNENFORCED. Primary key column order is based on the order of the columns during table creation. It is not based on the order of the columns as specified in the primary key declaration.

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 ) UNENFORCED, FOREIGN KEY REFERENCES employee UNENFORCED, FOREIGN KEY REFERENCES skill UNENFORCED

)

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.

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.

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

Note Adaptive Server IQ does not enforce foreign keys or multi-column primary keys. You must specify the keyword UNENFORCED when you add or delete these constraints. They can be useful, nonetheless, because they provide information that Adaptive Server IQ uses to optimize queries, and to define the underlying relationship between joined columns.

For more information about valid strings and identifiers, see the chapter “SQL Language Elements” in the Adaptive Server IQ Reference Manual.

Each foreign key relationship relates a primary key in one column to a column in another table, which becomes the foreign key.

126