Creating a Foreign Key Constraint

A foreign key constraint enforces a restriction on a column specifying how the data in one table relates to the data in another table, or the same table. Add a foreign key constraint to the table using the Create Constraint Wizard.

Prerequisites

Database Version	Table Constraint Privileges
SAP Sybase IQ 15.3 and 15.4	Requires one of: DBA authority. Requires one of: ALTER permission on the derived table You own the derived table. Also requires one of: REFERENCE permission on the base table. You own the base table. Also requires one of: REFERENCE permission on the derived table (to index it) You own the derived table.
SAP Sybase IQ 16.0	Requires one of: ALTER ANY OBJECT system privilege. CREATE permission on the dbspace the table is defined on, along with one of: ALTER ANY TABLE system privilege. ALTER permission on the derived table. You own the derived table. Also requires one of: CREATE ANY INDEX system privilege. CREATE ANY OBJECT system privilege. REFERENCE permission on the base table.

The SAP Sybase IQ resource is authenticated and running.
In a multiplex configuration, the coordinator node is running.
If you want the foreign key to reference a unique constraint, the unique constraint must exist.

Task

In the Perspective Resources view, select the resource and select Resource > Administration Console.
In the Administration Console, expand IQ Servers > Schema Objects > Tables.
Select Tables or Global Temporary Tables, select the table in the right pane, click the drop-down arrow that appears to the right, and select Properties.
Select Constraints.
Click New.
The Create Constraint Wizard appears.
On the Welcome page, specify a foreign key constraint:

Option Description

Select the type of constraint From the Select an item pull-down, select Foreign key constraint.
Click Next.

Option	Description
Select the type of constraint	From the Select an item pull-down, select Foreign key constraint.

On the Table page, select the table you want this foreign key to refer to:

Option	Description
To which table do you want this foreign key to refer?	Filter the Name and Owner columns to locate the desired table. Select a single table you want the foreign key to refer to.
What do you want to name the new foreign key?	Specify a descriptive name for the foreign key.

Click Next.

On the Reference page, specify:

Option	Description
Do you want this foreign key to reference the primary key or a unique constraint?	Primary key – Select this if you want the foreign key to reference the primary key. Unique constraint – Select this if you want the foreign key to reference an existing unique constraint. Select the unique constraint from the pull-down.
For each primary key column in the referenced table, you must specify the foreign column that it should reference or choose to add a new column to the table.	Filter the Foreign Column, Order, and Primary Key Column to locate the desired column. Foreign Column – Select the foreign column from the pull-down. Order – Select either Ascending or Descending order from the pull-down.
Add column	(Only displays if no exact match is found for the data type, size, scale, and name of the primary key column) Adds a new primary key column.

Click Next.

On the Referential Integrity page, specify:

Option	Description
Update action	Not permitted – Generates an error and prevents the modification if an attempt to alter a referenced primary key value occurs. This is the default referential integrity action. Set values to null – Sets all foreign keys that reference the modified primary key to NULL. Cascade values – Updates all foreign keys that reference the updated primary key to the new value. Set values to default – Sets all foreign keys that reference the modified primary key to the default value for that column (as specified in the table definition).
Delete action	Not permitted – Generates an error and prevents the modification if an attempt to alter a referenced primary key value occurs. This is the default referential integrity action. Set values to null – Sets all foreign keys that reference the modified primary key to NULL. Cascade values – Deletes all rows containing foreign keys that reference the deleted primary key. Set values to default – Sets all foreign keys that reference the modified primary key to the default value for that column (as specified in the table definition).
Check only on a commit	Forces the database to wait for a COMMIT before checking the integrity of the foreign key, overriding the setting of the WAIT_FOR_COMMIT database option.

Click Next.
(System store (IQ catalog store) tables only) On the Clustered Index page, specify:

Option Description

Create a clustered foreign key Select if you want to make the constraint's underlying index a clustered index. Clustered indexes can improve performance.
Click Next.
On the Comment page, add an optional, descriptive comment. Comments help you organize your database.
Click Finish to close the Create Constraint Wizard.
The Properties view appears.
Tip: Although you closed the Create Constraint Wizard, the constraint is still in-progress until you click Apply and OK in step 18. If there is a problem with the constraint, modify the in-progress constraint by clicking Properties and then making the necessary changes.
Click Apply and OK.