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 required. Modify comment on foreign key or unique constraint – Requires 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 required. Modify the comment on a foreign or primary key constraint – Requires 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 – Requires 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 – Requires 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 – Requires one of: CREATE ANY OBJECT system privilege. CREATE ANY INDEX system privilege. Also requires 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 – Requires one of: ALTER ANY OBJECT system privilege. ALTER ANY TABLE system privilege. ALTER permission on the underlying table. You own the underlying table.

Database Version

Table Constraint Privileges

SAP Sybase IQ 15.3 and 15.4

View table constraint properties – None required.

Modify comment on foreign key or unique constraint – Requires 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 required.

Modify the comment on a foreign or primary key constraint – Requires 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 – Requires 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 – Requires 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 – Requires one of:

CREATE ANY OBJECT system privilege.
CREATE ANY INDEX system privilege.
Also requires 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 – Requires one of:

ALTER ANY OBJECT system privilege.
ALTER ANY TABLE system privilege.
ALTER permission on the underlying table.
You own the underlying table.

The SAP Sybase IQ resource is authenticated and running.
(Modifying properties only) In a multiplex configuration, the coordinator node is running.

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.
Select a constraint from the list and click Edit.

View or modify constraint properties. The properties differ depending on the constraint type.

When modifying properties, clicking Apply before changing screens is not necessary, but will save any changes.
If you do not have privileges to modify properties, Sybase 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.

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.

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.

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.

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.

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.

Click OK.
The Properties view appears.
Tip: If you modified any properties, the constraint is still in-progress until you click Apply and OK in step 8. 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.