Use the CREATE TABLE statement or ALTER TABLE statement to create foreign keys, as you do primary keys.
You cannot create foreign key constraints on local temporary
tables. Global temporary tables must be created with ON
COMMIT PRESERVE ROWS.
For information on creating foreign keys, see “Creating primary and foreign keys”.
The sample database contains an employee table and a department table. The primary key for the employee table is the employee ID, and the primary key for the department table is the department ID. For example, assume the following schema:
DEPT table
{ DeptNo int primary key
DeptName varchar(20),
Mgr int,
foreign key MGR_EMPNO (Mgr) references EMPLOYEE(EmpNo) on update restrict }
EMPLOYEE table
{ EmpNo int primary key,
DeptNo int references DEPT(DeptNo) on delete restrict,
LastName varchar(20),
FirstName varchar(20),
Salary int }
In the employee table, the department ID is a foreign key for the department table; each department ID in the employee table corresponds exactly to a department ID in the department table.
The foreign key relationship is a many-to-one relationship. Several entries in the employee table have the same department ID entry, but the department ID is the primary key for the department table, and so is unique. If a foreign key could reference a column in the department table containing duplicate entries, there would be no way of knowing which row in the department table is the appropriate reference. This is a mandatory foreign key.
Sybase IQ supports referential integrity with RESTRICT action (the ANSI default) at the statement level. This means that Sybase IQ denies requests for updates and deletes on the primary key or column(s) with a unique constraint that removes any value upon which correspondent foreign key(s) depend. (You must be careful about the order in which you request deletes and updates.) Sybase IQ issues an error message and rolls back load operations that violate referential integrity, but lets you specify that certain rows be ignored. For more information, see “Disabling referential integrity checking”.
Enforcing referential integrity with existing
unenforced foreign keys
Identify the candidate key to foreign key relationship.
In the preceding schema, there are two such relationships:
Foreign key (EMPLOYEE.DeptNo to Candidate key(DEPT.DeptNo)
Foreign key(DEPT.Mgr) to Candidate key(EMPLOYEE.EMPNo)
Add a primary key or unique constraint on the candidate key via the ALTER TABLE statement if none exist. (In the preceding example, the primary key already exists.) All candidate key values must be unique and non-null.
Drop the unenforced foreign key constraint via the ALTER TABLE statement if one exists. For example:
ALTER TABLE DEPT DROP FOREIGN KEY MGR_EMPNO; ALTER TABLE EMPLOYEE DROP FOREIGN KEY DEPT;
In the preceding schema, we need to drop unenforced foreign key constraints MGR_EMPNO and EMPLOYEE(DeptNo) referencing DEPT(DeptNo). If there is no user specified role name for EMPLOYEE(DeptNo) to DEPT(DeptNo), the default role name is the same as the primary table, in other words, DEPT.
Add the foreign key constraint(s). For example:
ALTER TABLE DEPT ADD FOREIGN KEY MGR_EMPNO(Mgr) REFERENCES EMPLOYEE(EmpNo); ALTER TABLE EMPLOYEE ADD FOREIGN KEY EMP_DEPT(DeptNo) REFERENCES DEPT(DeptNo);
If you are creating a new table, you enforce referential integrity as follows:
Enforcing referential integrity in a new table
Create the primary table, for example:
CREATE TABLE DEPT(DeptNo int primary key, DeptName varchar(20), Mgr int );
Create the foreign table. For example, in this statement, the default role name for the specified foreign key is DEPT:
CREATE TABLE EMPLOYEE(EmpNo int primary key, DeptNo int references DEPT(DeptNo) on delete restrict, LastName varchar(20), FirstName varchar(20), Salary int);
Another way to create the foreign table follows. In this statement, the user specified role name for the same foreign key is EMP_DEPT:
CREATE TABLE EMPLOYEE(EmpNo int primary key, DeptNo int, LastName varchar(20), FirstName varchar(20), Salary int, FOREIGN KEY EMP_DEPT(DeptNo) REFERENCES DEPT(DeptNo));
Add the foreign key constraint. For example:
ALTER TABLE DEPT ADD FOREIGN KEY MGR_EMPNO(Mgr) REFERENCES EMPLOYEE(EmpNo);
To drop a foreign key constraint.
When there is no role name assigned, as in the first CREATE TABLE example, the default role name for the specified foreign key is DEPT:
ALTER TABLE EMPLOYEE DROP FOREIGN KEY DEPT;
If there are multiple foreign keys and the role name is unknown, you can use the sp_iqconstraint procedure to display it, as shown in Chapter 9, “System Procedures,” in Sybase IQ Reference Manual.
In the second CREATE TABLE example, the role name EMP_DEPT was assigned, so you must specify it when dropping the key, as follows:
ALTER TABLE EMPLOYEE DROP FOREIGN KEY EMP_DEPT;
These statements do not drop the non-unique HG index for EMPLOYEE(DeptNo) which is implicitly created. To drop it, use sp_iqindex to find the HighGroup index name and use the DROP INDEX statement, as follows:
sp_iqindex(‘EMPLOYEE’);
EMPLOYEE DBA DeptNO FP ASIQ_IDX_T27_C2_FP N EMPLOYEE DBA DeptNO HG ASIQ_IDX_T27_C2_HG N EMPLOYEE DBA EmpNO FP ASIQ_IDX_T27_C1_FP N EMPLOYEE DBA EmpNO HG ASIQ_IDX_T27_I11_HG N EMPLOYEE DBA FirstName FP ASIQ_IDX_T27_C4_FP N EMPLOYEE DBA LastName FP ASIQ_IDX_T27_C3_FP N EMPLOYEE DBA Salary FP ASIQ_IDX_T27_C5_FP N
DROP INDEX ASIQ_IDX_T27_C2_HG
To drop a table, you must drop all associated foreign key constraints. Drop foreign key constraint and tables in this order:
ALTER TABLE DROP FOREIGN KEY MGR_EMPNO; DROP TABLE EMPLOYEE; DROP TABLE DEPT;
Another way to drop the same tables would be to use the following two ALTER TABLE statements in any order and then do DROP TABLE statements in any order:
ALTER TABLE DEPT DROP FOREIGN KEY MGR_EMPNO; ALTER TABLE EMPLOYEE DROP FOREIGN KEY EMP_DEPT;
Suppose the database also contained an office table, listing office locations. The employee table might have a foreign key for the office table that indicates where the employee's office is located. The database designer may allow for an office location not being assigned when the employee is hired. In this case, the foreign key should allow the NULL value for when the office location is unknown or when the employee does not work out of an office.
