[top]
[prev]
[next]

Documentation Top
Global Index
Reference Manual
TOC Index
Grammar Index
Developer’s Guide
TOC Index
Tuning Cloudscape
TOC Index
|
SYSCONSTRAINTS
Describes the information common to all types of constraints within the current database (currently, this includes primary key, unique, foreign key, and check constraints).
Column Name |
Type |
Length |
Nullable |
Contents |
CONSTRAINTID |
CHAR |
36 |
false |
unique identifier for constraint |
TABLEID |
CHAR |
36 |
false |
identifier for table (join with SYSTABLES.TABLEID) |
CONSTRAINTNAME |
VARCHAR |
128 |
false |
constraint name (internally generated if not specified by user) |
TYPE |
CHAR |
1 |
false |
P (primary key), U (unique), C (check), or F (foreign key) |
SCHEMAID |
CHAR |
36 |
false |
identifier for schema that the constraint belongs to (join with SYSSCHEMAS.SCHEMAID) |
STATE |
CHAR |
1 |
false |
E for enabled, D for disabled |
REFERENCECOUNT |
INTEGER |
1 |
false |
the count of the number of foreign key constraints that reference this constraint; this number can be greater than zero only for PRIMARY KEY and UNIQUE constraints |
Indexes
- SYSCONSTRAINTS_INDEX1 unique BTREE index on (CONSTRAINTID)
- SYSCONSTRAINTS_INDEX2 unique BTREE index on (CONSTRAINTNAME, SCHEMAID)
- SYSCONSTRAINTS_INDEX3 BTREE index on (TABLEID)
Sample Queries
Find out the names of all the primary key constraints in the database and the names of the indexes backing up those constraints:
SELECT c.CONSTRAINTNAME, cn.CONGLOMERATENAME FROM SYS.SYSCONSTRAINTS c , SYS.SYSCONGLOMERATES cn, SYS.SYSKEYS k WHERE c.TYPE = 'P' AND c.CONSTRAINTID = k.CONSTRAINTID AND cn.CONGLOMERATEID = k.CONGLOMERATEID AND c.TABLEID = cn.TABLEID
Find out the name of the index backing up the constraint called flights_pk in the Flights table:
SELECT cn.CONGLOMERATENAME FROM SYS.SYSCONSTRAINTS c , SYS.SYSCONGLOMERATES cn, SYS.SYSKEYS k WHERE c.CONSTRAINTID = k.CONSTRAINTID AND cn.CONGLOMERATEID = k.CONGLOMERATEID AND c.TABLEID = cn.TABLEID AND c.CONSTRAINTNAME = 'FLIGHTS_PK'
Find out the foreign keys and the names of the backing indexes used to enforce them:
SELECT C.CONSTRAINTNAME, CN.CONGLOMERATENAME FROM SYS.SYSCONSTRAINTS c, SYS.SYSFOREIGNKEYS f, SYS.SYSCONGLOMERATES cn WHERE f.CONSTRAINTID = c.CONSTRAINTID AND f.CONGLOMERATEID = cn.CONGLOMERATEID
|