 |
» |
|
|
|
SYSTEM.CONSTRAINTINDEX contains an entry for each integrity constraint.
This view is initially empty, but is updated whenever ALLBASE/SQL
processes a CREATE TABLE, DROP TABLE, ALTER TABLE TRANSFER
OWNERSHIP (of a table), or UPDATE STATISTICS statement involving
an integrity constraint. Table 8-10 System.ConstraintIndex |
Column Name | Type | Length | Description |
|---|
| CONSTRAINTNAME | CHAR | 20 | Name of the unique or referential constraint
| | TABLENAME | CHAR | 20 | Name of the table on which the
index is defined
| | OWNER | CHAR | 20 | Owner of the table on which the
index is defined
| | NUMC | INTEGER | 4 | Number of columns in the index
| | COLNUMS | BINARY | 32 |
A vector of 16 SYSTEM.COLUMN entries,
each of type SMALLINT, identifying
the column numbers the index is
defined over. In ISQL, each SMALLINT (two-byte) entry is
displayed as a field of 4 hexadecimal digits.
| | NPAGES | INTEGER | 4 | Number of data pages
containing the constraint index
| | CCOUNT | INTEGER | 4 | Cluster count; indicates how
well the data of the index are
sorted: | | | | |
- 0
before first UPDATE STATISTICS statement is processed
- n
efficiency of clustering:
best clustering if
n=NPAGES of table
indexed; worst if
n=NROWS of table indexed
| | CTIME | CHAR | 16 | Time the index was created
| | UNIQUE | SMALLINT | 2 | Uniqueness indicator: | | | | | 0 if duplicates are allowed,
that is, the index is not
unique | | | | | 1 if duplicates are not
allowed, that is the index
is unique
| | CLUSTER | SMALLINT | 2 | Clustering indicator: | | | | | 0 if the index is not a
clustering index | | | | | 1 if the index is the
clustering index for the
table
| | COLDIRS | BINARY | 32 | A vector of 16 direction entries,
each of type SMALLINT, indicating
the direction of the corresponding
column in the index definition.
In ISQL, each SMALLINT (two-byte) entry is
displayed as a field of 4 hexadecimal digits. The following values indicate a specific direction: | | | | | 5 ASC (Ascending) | | | | | 6 DESC (Descending)
|
Example | |
SELECT * FROM System.ConstraintIndex;
--------------------+--------------------+--------------------+-----------
CONSTRAINTNAME |TABLENAME |OWNER |NUMC
--------------------+--------------------+--------------------+-----------
SQLCON_00000001P00 |PARTS1 |PURCHDB | 1
CLUBS_PK |CLUBS |RECDB | 1
MEMBERS_PK |MEMBERS |RECDB | 2
MEMBERS_FK |MEMBERS |RECDB | 1
EVENTS_FK |EVENTS |RECDB | 2
---------------------------------------------------------------------------
Number of rows selected is 5
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]> r
+----------------------------------------------------------------+------------
|COLNUMS |NPAGES
+----------------------------------------------------------------+------------
|0001000000000000000000000000000000000000000000000000000000000000| 0
|0001000000000000000000000000000000000000000000000000000000000000| 0
|0001000000000000000000000000000000000000000000000000000000000000| 0
|0002000000000000000000000000000000000000000000000000000000000000| 0
|0001000000000000000000000000000000000000000000000000000000000000| 0
------------------------------------------------------------------------------
Number of rows selected is 5
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]> r
+-----------+----------------+------+-------
|CCOUNT |CTIME |UNIQUE|CLUSTER
+-----------+----------------+------+-------
| 0|1990040914185800| 1| 0
| 0|1990040914203800| 1| 0
| 0|1990040914280600| 1| 0
| 0|1990040914280600| 0| 0
| 0|1990040914295500| 0| 0
---------------------------------------------------------------------------
Number of rows selected is 5
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]> e
+----------------------------------------------------------------+
|COLDIRS |
+----------------------------------------------------------------+
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
|
|
