Objects, Keys, Indices, and Sequences
sys.objects
| name | type | references | description |
|---|
| "id" | INTEGER | | The internal object identifier. Primary key of this table is the combination of "id" and "nr". |
| "name" | VARCHAR | | The SQL name. |
| "nr" | INTEGER | | Position within the object list. |
sys.keys
| name | type | references | description |
|---|
| "id" | INTEGER | sys.objects.id | The unique internal key identifier. |
| "table_id" | INTEGER | sys.tables.id | The corresponding table identifier. |
| "type" | INTEGER | sys.key_types.key_type_id | Key type id: 0=Primary Key, 1=Unique Key, 2=Foreign Key, 3=Unique Key with Nulls Not Distinct, 4=Check Constraint, see table sys.key_types. |
| "name" | VARCHAR | | The SQL name given to the key. |
| "rkey" | INTEGER | sys.keys.id when rkey > 0 | Related primary key or unique constraint id. -1 if not applicable. |
| "action" | INTEGER | | The combined foreign key on UPDATE and on DELETE rule action codes. See sys.fkeys for easier access to the rule action values. |
| "check" | VARCHAR | | The relational plan and SQL expression of the check constraint. Tip: To extract the SQL expression query "check_clause" from "information_schema"."check_constraints". |
sys.key_types
| name | type | references | description |
|---|
| "key_type_id" | SMALLINT | | The unique internal identifier of a key type. |
| "key_type_name" | VARCHAR | | A description of the key type. |
sys.fkeys
| name | type | references | description |
|---|
| "id" | INTEGER | sys.objects.id | The unique internal key identifier. |
| "table_id" | INTEGER | sys.tables.id | The corresponding table identifier. |
| "type" | INTEGER | sys.key_types.key_type_id | Key type id: 2=Foreign Key, see table sys.key_types. |
| "name" | VARCHAR | | The SQL name given to the foreign key. |
| "rkey" | INTEGER | sys.keys.id | Related primary key or unique constraint id. |
| "update_action_id" | SMALLINT | sys.fkey_actions.action_id | The foreign key UPDATE rule action code: 0=NO ACTION, 1=CASCADE, 2=RESTRICT, 3=SET NULL, 4=SET DEFAULT. |
| "update_action" | VARCHAR | sys.fkey_actions.action_name | The derived foreign key UPDATE rule name. |
| "delete_action_id" | SMALLINT | sys.fkey_actions.action_id | The foreign key DELETE rule action code: 0=NO ACTION, 1=CASCADE, 2=RESTRICT, 3=SET NULL, 4=SET DEFAULT. |
| "delete_action" | VARCHAR | sys.fkey_actions.action_name | The derived foreign key DELETE rule action name. |
sys.fkey_actions
| name | type | references | description |
|---|
| "action_id" | SMALLINT | | The unique internal identifier of an action type. |
| "action_name" | VARCHAR | | The action name, as used in SQL foreign key constraints. |
sys.idxs
| name | type | references | description |
|---|
| "id" | INTEGER | sys.objects.id | The unique internal index identifier. |
| "table_id" | INTEGER | sys.tables.id | The corresponding table identifier. |
| "type" | INTEGER | sys.index_types.index_type_id | The corresponding index type identifier, see table sys.index_types. |
| "name" | VARCHAR | | The SQL name. |
sys.idx_types
| name | type | references | description |
|---|
| "index_type_id" | SMALLINT | | The unique internal identifier of an index type. |
| "index_type_name" | VARCHAR | | A description of the index type. |
sys.sequences
| name | type | references | description |
|---|
| "id" | INTEGER | | The unique internal sequence identifier. |
| "schema_id" | INTEGER | sys.schemas.id | The corresponding schema identifier. |
| "name" | VARCHAR | | The SQL name. |
| "start" | BIGINT | | The start value of the sequence. |
| "minvalue" | BIGINT | | The minimal value of the sequence. |
| "maxvalue" | BIGINT | | The maximal value of the sequence. |
| "increment" | BIGINT | | The sequence increment value. |
| "cacheinc" | BIGINT | | The increment to ease transaction management. |
| "cycle" | BOOLEAN | | The repeat the sequence upon hitting its bounds. |