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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Tables, views, and indexes » Foreign keys

 

Creating a foreign key (SQL)

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.

Prerequisites

The privileges required to create a foreign key depend on table ownership and are as follows:

  • You own both the referenced (primary key) and referencing (foreign key) table   You do not need any privileges.

  • You own the referencing table, but not the referenced table   You must have REFERENCES privilege on the table or one of CREATE ANY INDEX or CREATE ANY OBJECT system privileges.

  • You own the referenced table, but not the referencing table  

    • You must have one of ALTER ANY OBJECT or ALTER ANY TABLE system privileges.
    • Or, you must have ALTER privilege on the table along with one of COMMENT ANY OBJECT, CREATE ANY OBJECT, or CREATE ANY TABLE system privileges.
    • You must also have SELECT privilege on the table, or the SELECT ANY TABLE system privilege.

  • You own neither table  

    • You must have REFERENCES privilege on the table or one of CREATE ANY INDEX or CREATE ANY OBJECT system privileges.
    • You must have one of ALTER ANY OBJECT or ALTER ANY TABLE system privileges.
    • Or, you must have ALTER privilege on the table along with one of COMMENT ANY OBJECT, CREATE ANY OBJECT, or CREATE ANY TABLE system privileges.
    • You must also have SELECT privilege on the table, or the SELECT ANY TABLE system privilege.

You must have SELECT privilege on the table or the SELECT ANY TABLE system privilege.

You must also be the owner of the table, or have one of the following privileges:

  • ALTER privilege on the table along with one of COMMENT ANY OBJECT, CREATE ANY OBJECT, or CREATE ANY TABLE system privileges
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege

Context and remarks

You do not have to create a foreign key when you create a foreign table; the foreign key is created automatically.

 Task
  1. Connect to the database.

  2. Execute an ALTER TABLE statement similar to the following:

    ALTER TABLE table-name ADD FOREIGN KEY foreign-key-name 
    ( column-name ASC ) REFERENCES table-name ( column-name )

Results

The table definition is updated to include the foreign key definition.

Example

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 by 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 data.

ALTER TABLE Skills2
ADD NOT NULL FOREIGN KEY SkillFK ( SkillID )
REFERENCES Skills2 ( ID )
ON UPDATE RESTRICT
ON DELETE RESTRICT;

Foreign key column names are paired with primary key column names according to position in the two lists in a one-to-one manner. If the primary table column names are not specified when defining the foreign key, then the primary key columns are used. For example, suppose you create two tables as follows:

CREATE TABLE Table1( a INT, b INT, c INT, PRIMARY KEY ( a, b ) );
CREATE TABLE Table2( x INT, y INT, z INT, PRIMARY KEY ( x, y ) );

Then, you create a foreign key fk1 as follows, specifying exactly how to pair the columns between the two tables:

ALTER TABLE Table2 ADD FOREIGN KEY fk1( x,y ) REFERENCES Table1( a, b );

Using the following statement, you create a second foreign key, fk2, by specifying only the foreign table columns. The database server automatically pairs these two columns to the first two columns in the primary key on the primary table.

ALTER TABLE Table2 ADD FOREIGN KEY fk2( x, y ) REFERENCES Table1;

Using the following statement, you create a foreign key without specifying columns for either the primary or foreign table:

ALTER TABLE Table2 ADD FOREIGN KEY fk3 REFERENCES Table1;

Since you did not specify referencing columns, the database server looks for columns in the foreign table (Table2) with the same name as columns in the primary table (Table1). If they exist, the database server ensures that the data types match and then creates the foreign key using those columns. If columns do not exist, they are created in Table2. In this example, Table2 does not have columns called a and b so they are created with the same data types as Table1.a and Table1.b. These automatically-created columns cannot become part of the primary key of the foreign table.


 See also