Differences between revisions 4 and 5
Revision 4 as of 2006-05-31 19:20:01
Size: 2511
Editor: MamtaSatoor
Comment:
Revision 5 as of 2009-09-20 22:12:18
Size: 2511
Editor: localhost
Comment: converted to 1.6 markup
No differences found!

/!\ Under Construction /!\

This page contains a partial description of Derby's system tables, captured from a mail thread in Jan 2006: http://www.nabble.com/System-tables-in-Derby-t1027940.html#a2666072. Please revise and update as needed.

SYSTABLES has one row for each table in the database. Its primary key is TABLEID, which contains system-generated values. The SCHEMAID is a foreign key column, which references SYSSCHEMAS.

SYSSCHEMAS has one row for each schema in the database. Its primary key is SCHEMAID.

SYSCOLUMNS has one row for each column of every table in the database. Its primary key is (REFERENCEID, COLUMNNUMBER). REFERENCEID is also a foreign key, which refers to TABLEID in SYSTABLES.

SYSCONSTRAINTS has one row for each constraint in the database (primary, unique, foreign and check constraints). Its primary key is CONSTRAINTID, which is a system-generated value. The TABLEID column is a foreign key referring to SYSTABLES.TABLEID. The SCHEMAID column is a foreign key referring to SYSSCHEMAS.SCHEMAID.

SYSCONGLOMERATES has one row for each heap (base table) and index in the database. CONGLOMERATEID is a system-generated value. There is no unique key defined on CONGLOMERATEID but it contains unique values. The TABLEID column is a foreign key referring to SYSTABLES.TABLEID. The SCHEMAID column is a foreign key referring to SYSSCHEMAS.SCHEMAID. The ISCONSTRAINT column tells whether the row represents the backing index for a primary key, foreign key or unique constraint.

SYSKEYS has one row for each primary key or unique constraint in the database. Its primary key is CONSTRAINTID, which is also a foreign key to SYSCONSTRAINTS.CONSTRAINTID. The CONGLOMERATEID column is a foreign key referring to SYSCONGLOMERATES.CONGLOMERATEID.

SYSFOREIGNKEYS has one row for each foreign key in the database. Its primary key is (CONSTRAINTID, KEYCONSTRAINTID). The CONSTRAINTID column is a foreign key refererring to SYSCONSTRAINTS.CONSTRAINTID, and it contains the constraint id of the foreign key itself. The KEYCONSTRAINTID is a foreign key referring to SYSCONSTRAINTS.CONSTRAINTID, and it contains the constraint id of the primary key that the foreign key refers to. The CONGLOMERATEID column is a foreign key that refers to SYSCONGLOMERATES.CONGLOMERATEID, and it contains the id of the backing index for the foreign key.

The exact structure of these tables can be found in the Reference Manual, section Derby System Tables.

SystemTables (last edited 2009-09-20 22:12:18 by localhost)