You can create and alter foreign keys 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 can have as many foreign keys as necessary.
To alter the foreign key of an existing table (SQL)
Connect to the database as a user with DBA authority.
Execute an ALTER TABLE statement.
In the following example, you create a table called Skills which contains a list of possible skills, and then create a table called EmployeeSkills that has a foreign key relationship to the Skills table. Notice that EmployeeSkills.SkillID has a foreign key relationship with the primary key column (Id) of the Skills table.
CREATE TABLE Skills ( Id INTEGER PRIMARY KEY, SkillName CHAR(40), Description CHAR(100) ); CREATE TABLE EmployeeSkills ( EmployeeID INTEGER NOT NULL, SkillId INTEGER NOT NULL, SkillLevel INTEGER NOT NULL, PRIMARY KEY( EmployeeID ), FOREIGN KEY (SkillID) REFERENCES Skills ( Id ) );
You can also add a foreign key to a table after it has been created, using the ALTER TABLE statement. In the following example, you create tables similar to those created in the previous example, except you add the foreign key after creating the table.
CREATE TABLE Skills2 ( Id INTEGER PRIMARY KEY, SkillName CHAR(40), Description CHAR(100) ); CREATE TABLE EmployeeSkills2 ( EmployeeID INTEGER NOT NULL, SkillId INTEGER NOT NULL, SkillLevel INTEGER NOT NULL, PRIMARY KEY( EmployeeID ), ); ALTER TABLE EmployeeSkills2 ADD FOREIGN KEY SkillFK ( SkillID ) REFERENCES Skills2 ( Id );
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 Skills2 ADD NOT NULL FOREIGN KEY SkillFK ( SkillID ) REFERENCES Skills2 ( ID ) ON UPDATE RESTRICT ON DELETE RESTRICT;
In Sybase Central, you can also specify properties in the Create Foreign Key Wizard or on the Foreign Key Properties window. See Managing foreign keys (Sybase Central).
|Send feedback about this page via email or DocCommentXchange||Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0|