 |
» |
|
|
|
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
|
|
