Cloudscape System Tables
Page 6 of 17

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