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 You must have DBA authority or have one of:
  • ALTER permission on the derived table
  • You own the derived table
In addition, if you do not have DBA authority, you must also have one of:
  • REFERENCE permission on the base table
  • You own the base table
Finally, if you do not have DBA authority, you must also have one of:
  • REFERENCE permission on the derived table (to index it)
  • You own the derived table
SAP Sybase IQ 16.0 You must have 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
You must also have one of:
  • CREATE ANY INDEX system privilege
  • CREATE ANY OBJECT system privilege
  • REFERENCE permission on the base table
Task
  1. In the Perspective Resources view, select the resource, and select Resource > Administration Console.
  2. In the left pane, expand IQ Servers > Schema Objects > Tables, and then select Tables or Global Temporary Tables.
  3. Select a table from the right pane and either:
    • Click the arrow to the right of the name and select Properties, or
    • From the Administration Console menu bar, select Resource > Properties.
    The Table or Global Temporary Table Properties view appears.
  4. Select Constraints.
  5. Click New.
    The Create Constraint Wizard appears.
  6. On the Welcome page, select Foreign key constraint from the drop-down list. Click Next.
  7. 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.
  8. Click Next.
  9. 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 drop-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 drop-down.
    • Order – Select either Ascending or Descending order from the drop-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.
  10. Click Next.
  11. 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.
  12. Click Next.
  13. (System store (IQ catalog store) tables only) On the Clustered Index page, select the option if you want to make the constraint's underlying index a clustered index. Clustered indexes can improve performance. Click Next.
  14. On the Comment page, add an optional, descriptive comment. Comments help you organize your database.
  15. Click Finish to close the Create Constraint Wizard.
    The Properties view appears.
    Tip: Although you closed the Create Constraint Wizard, the constraint creation process is not complete until you click Apply and OK on the Table Properties view.
  16. Do one of:
    • Click OK to update any changes to the database and exit the properties view.
    • Click Apply to update any changes to the database, but remain in the properties view.
    • Click Cancel to cancel any changes not updated to the database and exit the properties view.
Parent topic: Table Constraints
Related tasks
Creating a Column Check Constraint
Creating a Table Check Constraint
Creating a Unique Constraint
Deleting a Table or Column Check Constraint
Deleting a Primary, Foreign, or Unique Key Constraint
Rebuilding a Unique, Primary or Foreign Key Constraint
Validating a Unique, Primary or Foreign Key Constraint
Generating Constraint DDL Commands
Viewing or Modifying Constraint Properties
Authenticating a Login Account for a Managed Resource
Related reference
Table Constraints Privilege Summary