Documentation > User Guide > SQL Catalog > Objects, Keys, Indices, and Sequences

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.

key_type_id	key_type_name
0	Primary Key
1	Unique Key
2	Foreign Key
3	Unique Key With Nulls Not Distinct
4	Check Constraint

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.

action_id	action_name
0	NO ACTION
1	CASCADE
2	RESTRICT
3	SET NULL
4	SET DEFAULT

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.index_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.

index_type_id	index_type_name
0	Hash
1	Join
2	Order preserving hash
3	No-index
4	Imprint
5	Ordered

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.