CREATE TABLE SYS.SYSFOREIGNKEY ( foreign_table_id UNSIGNED INT NOT NULL, foreign_key_id SMALLINT NOT NULL, primary_table_id UNSIGNED INT NOT NULL, root INT NOT NULL, check_on_commit CHAR(1) NOT NULL, nulls CHAR(1) NOT NULL, role CHAR(128) NOT NULL, remarks LONG VARCHAR, primary_index_id UNSIGNED INT NOT NULL, fk_not_enforced CHAR(1) NOT NULL hash_limit SMALLINTO NOT NULL, PRIMARY KEY ( foreign_table_id, foreign_key_id ), UNIQUE ( role, foreign_table_id ), FOREIGN KEY foreign_table ( foreign_table_id ) REFERENCES SYS.SYSTABLE ( table_id ), FOREIGN KEY primary_table ( primary_table_id ) REFERENCES SYS.SYSTABLE ( table_id ) )
A foreign key is a relationship between two tables—the foreign table and the primary table. Every foreign key is defined by one row in SYSFOREIGNKEY and one or more rows in SYSFKCOL. SYSFOREIGNKEY contains general information about the foreign key while SYSFKCOL identifies the columns in the foreign key and associates each column in the foreign key with a column in the primary key of the primary table.
foreign_table_id The table number of the foreign table.
foreign_key_id Each foreign key has a foreign key number that is unique with respect to:
The key number of all other foreign keys for the foreign table
The key number of all foreign keys for the primary table
The index number of all indexes for the foreign table
primary_table_id The table number of the primary table.
root Foreign keys are stored in the database as B-trees. The root identifies the location of the root of the B-tree in the database file.
check_on_commit (Y/N) Indicates whether INSERT and UPDATE commands should wait until the next COMMIT command to check if foreign keys are valid. A foreign key is valid if, for each row in the foreign table, the values in the columns of the foreign key either contain the NULL value or match the primary key values in some row of the primary table.
nulls (Y/N) Indicates whether the columns in the foreign key are allowed to contain the NULL value. Note that this setting is independent of the nulls setting in the columns contained in the foreign key.
role The name of the relationship between the foreign table and the primary table. Unless otherwise specified, the role name will be the same as the name of the primary table. The foreign table cannot have two foreign keys with the same role name.
primary_index_id The index_id of the primary key, or root if the primary key is part of a combined index.
fk_not_enforced (Y/N) Is N if one of the tables is remote.
hash_limit Contains information about physical index representation.
