Viewing or Modifying Constraint Properties

View constraint details or change the constraint details and constraint definition of an existing constraint.

Prerequisites
Database Version Table Constraint Privileges
SAP Sybase IQ 15.3 and 15.4

View table constraint properties – none

Modify comment on foreign key or unique constraint – you must have one of:
  • DBA authority
  • ALTER permission on the underlying table
  • You own the underlying table
SAP Sybase IQ 16.0

View any table constraint property page – none

Modify the comment on a foreign or primary key constraint – you must have one of:
  • CREATE ANY TABLE system privilege
  • ALTER ANY TABLE system privilege
  • CREATE ANY OBJECT system privilege
  • ALTER ANY OBJECT system privilege
  • COMMENT ANY OBJECT system privilege
  • You own the table
Modify the comment on a unique key constraint – you must have one of:
  • CREATE ANY INDEX system privilege
  • ALTER ANY INDEX system privilege
  • CREATE ANY OBJECT system privilege
  • ALTER ANY OBJECT system privilege
  • COMMENT ANY OBJECT system privilege
  • You own the table
Modify the definition of a table or column check constraint – you must have one of:
  • ALTER ANY OBJECT system privilege
  • ALTER ANY TABLE system privilege
  • ALTER permission on the table
  • You own the table
Modify any foreign key constraint property on the Action page – you must have one of:
  • CREATE ANY OBJECT system privilege
  • CREATE ANY INDEX system privilege
You must also have one of:
  • ALTER ANY OBJECT system privilege
  • ALTER ANY TABLE system privilege
  • ALTER permission on the underlying table
  • You own the table
Modify any other property of a primary, foreign or unique key, table check or column check constraint – you must have one of:
  • ALTER ANY OBJECT system privilege
  • ALTER ANY TABLE system privilege
  • ALTER permission on the underlying table
  • You own the underlying 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. Select a constraint from the list and click Edit.
  6. View or modify constraint properties. The properties differ depending on the constraint type.
    • When you are modifying properties, you need not click Apply before changing screens; however, doing so saves any changes.
    • If you do not have privileges to modify properties, SAP Control Center displays the properties view in read-only mode.
    Column Check Constraint Properties
    Area Description
    n/a

    Constraint Name – Name of the column check constraint.

    Constraint Definition – SQL definition of the column check constraint.

    Foreign Key Constraint Properties
    Area Description
    General

    Name – Name of the foreign key constraint.

    Unique – Shows whether the foreign key is unique.

    Foreign table – Shows the name of the table the foreign key belongs to, as well as the table's owner.

    Foreign index – Shows the name of the index used to enforce the foreign key.

    Primary constraint – Shows the name of the primary key or unique constraint that the foreign key references.

    Primary constraint type – Shows the type of constraint the foreign key references. This can only be a primary key or a unique constraint.

    Primary table – The table containing the primary key in the foreign key relationship.

    Primary index – Shows the name of the index used to maintain the primary key or unique constraint.

    Comment – Text description of the foreign key constraint. For example, describe the constraint's purpose in the system.
    Actions Allow null values – Determines whether the foreign key columns allow NULL values. To use this option, the foreign key columns must all have Allow Nulls set to Yes.
    • Match type – Shows the match type selected for the foreign key. The match type determines what is considered a match when using a multi-column foreign key where Null values are allowed. This only applies if the foreign key allows Null.
      • Simple – A match occurs for a row in the referencing table if at least one column in the key is NULL, or all the column values match the corresponding column values present in a row of the referenced table.
      • Full – A match occurs for a row in the referencing table if all column values in the key are NULL, or if all of the column values match the values present in a row of the referenced table.
    Update Action – Uses one of the following settings to define the behavior of the table when a user tries to update values in the foreign key.
    • Not permitted – Prevents updates of the associated primary table's primary key value if there are no corresponding foreign keys
    • Set values to null – Sets all the foreign key values that correspond to the updated primary key of the associated primary table to NULL. To use this option, the foreign key columns must all have Allow null values set to Yes
    • Cascade values – Updates the foreign key to match a new value for the associated primary key.
    • Set values to default – Sets foreign key values that match the updated or deleted primary key value to values specified in the DEFAULT clause of each foreign key column. To use this option, the foreign key columns must all have default values.
    Delete Action – Uses one of the following settings to define the behavior of the table when a user tries to delete data.
    • Not permitted – Prevents deletion of the associated primary table's primary key value if there are no corresponding foreign keys in the table.
    • Set values to null – Sets all the foreign key values in the table that correspond to the deleted primary key of the associated primary table to NULL. To use this option, the foreign key columns must all have Allow Nulls set to Yes.
    • Cascade values – Deletes the rows from the table that match the deleted primary key of the associated primary table.
    • Set values to default – Sets foreign key values that match the updated or deleted primary key value to values specified in the DEFAULT clause of each foreign key column. To use this option, the foreign key columns must all have default values.

    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.
    Columns Column details for columns contained by the foreign key constraint.

    Table Check Constraint Properties

    Area Description
    n/a Constraint Name – Name of the column check constraint.

    Type – (read-only) Identifies the constraint as a table check constraint.

    Table – (read-only) Table name.

    Constraint Definition – SQL definition of the table check constraint.

    Unique Constraint Properties

    Area Description
    General

    Name – Unique constraint name.

    Index – (read only) Index name.

    Comment – Text description of the unique constraint. For example, describe the constraint's purpose in the system.

    Columns

    Column details for the column contained by the unique constraint.

    Primary Key Constraint Properties

    Area Description
    General

    Name – Primary key name.

    Index – (read only) Index name.

    Comment – Text description of the primary key constraint. For example, describe the constraint's purpose in the system.

    Columns

    Column details for the column contained by the primary key constraint.
  7. In the Constraints view, do one of:
    • Click OK to keep any changes to the contraints and exit the Constraints view.
    • Click Apply to keep any changes the constraints, but remain in the Constraints view.
    • Click Cancel to cancel any changes to constraints and exit the view.
    Tip: If you modified any properties, clicking OK or Apply in the Contratins view saves the changes, but does not update them to the database. They remain "in-progress" until you click Apply or OK in the Table Properties view.
  8. 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 Foreign Key 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
Authenticating a Login Account for a Managed Resource
Related reference
Table Constraints Privilege Summary