Declaring a Constraint Name

The database server implements the constraint as an index. Whenever you use the single- or multiple-column constraint format to place a data restriction on a column, but without declaring a constraint name, the database server creates a constraint and adds a row for that constraint in the sysconstraints system catalog table. The database server also generates an identifier and adds a row to the sysindexes system catalog table for each new primary-key, unique, or referential constraint that does not share an index with an existing constraint. Even if you declare a name for a constraint, the database server generates the name that appears in the sysindexes table.

If you want, you can specify a meaningful name for the constraint. The name must be unique among the names of constraints and indexes in the database.

Constraint names appear in error messages having to do with constraint violations. You can use this name when you use the DROP CONSTRAINT clause of the ALTER TABLE statement.

In Dynamic Server, you also specify a constraint name when you change the mode of constraint with the SET Database Object Mode statement or the SET Transaction Mode statement, and in the DROP INDEX statement for constraints that are implemented as indexes with user-defined names.

In an ANSI-compliant database, when you declare the name of a constraint of any type, the combination of the owner name and constraint name must be unique within the database.

In Dynamic Server, the system catalog table that holds information about indexes is the sysindices table.

Constraint Names That the Database Server Generates

If you do not specify a constraint name, the database server generates a constraint name using the following template:

<constraint_type><tabid>_<constraintid> 

In this template, constraint_type is the letter u for unique or primary-key constraints, r for referential constraints, c for check constraints, and n for NOT NULL constraints. In the template, tabid and constraintid are values from the tabid and constrid columns of the systables and sysconstraints system catalog tables, respectively. For example, the constraint name for a unique constraint might look like " u111_14" (with a leading blank space).

If the generated name conflicts with an existing identifier, the database server returns an error, and you must then supply an explicit constraint name.

The generated index name in sysindexes (or sysindices) has this format:

[blankspace]<tabid>_<constraintid>

For example, the index name might be something like " 111_14 " (quotation marks used here to show the blank space).