dbo.fSQLForeignKeys

dbo.fSQLForeignKeys

This functions returns the foreign key information for the specified table in the current database. Dbo.fSQLForeignKeys can return a list of foreign keys as a result set for either of the following:

•

the specified table (columns in the specified table that refer to primary keys in other tables)

•

other tables that refer to the primary key in the specified table.

dbo.fSQLForeignKeys (<'table_qualifier' | null>, 'pkey_table_name' | null>, <'fkey_table_name' | null>)

Table 112

dbo.fSQLForeignKeys Arguments

table_qualifier

Current database

Name of the database from which the details are to be obtained.

pkey_table_name

(no default value)

Name of the table whose foreign key is associated with the primary key column. Pattern matching is supported (see String Search Patterns).

fkey_table_name

(no default value)

Name of the table whose foreign key information needs to be obtained. Pattern matching is supported (see String Search Patterns).

Returned Result Set

Table 113

dbo.fSQLForeignKeys Returned Result Set

PKTABLE_QUALIFIER

Database name of the primary key table; NULL if not applicable to the database.

Name of the owner of the primary key table; NULL if not applicable to the database.

VARCHAR not NULL

Name of the primary key table

VARCHAR not NULL

Column name of the primary key column. An empty string is returned for a column that does not have a name.

FKTABLE_QUALIFIER

Database name of the foreign key table; NULL if not applicable to the database.

Name of the owner of the foreign key table; NULL if not applicable to the database.

VARCHAR not NULL

Name of the foreign key table.

VARCHAR not NULL

Column name of the foreign key column. An empty string is returned for a column that does not have a name.

Column sequence number in key (starting with 1). The value of this column corresponds to Xi$Part in X$Index. See X$Index.

Action to be applied to the foreign key when the SQL operation is UPDATE. Can have one of the following values:

•

0 = SQL_CASCADE

•

1 = SQL_RESTRICT

Action to be applied to the foreign key when the SQL operation is DELETE. Can have one of the following values:

•

0 = SQL_CASCADE

•

1 = SQL_RESTRICT

Name of the foreign key; NULL if not applicable to the database.

Name of the primary key; NULL if not applicable to the database.

One of the following values:

•

5 = SQL_INITIALLY_DEFERRED

•

6 = SQL_INITIALLY_IMMEDIATE

•

7 = SQL_NOT_DEFERRABLE

This example creates three tables in the DEMODATA sample database. Primary keys and foreign keys are assigned to the tables. The dbo.fSQLForeignKeys function references the two primary key tables using a string search pattern. See also String Search Patterns.

CREATE TABLE primarykey1 (pk1col1 INT, pk1col2 INT, pk1col3 INT, pk1col4 INT, PRIMARY KEY (pk1col1, pk1col2))

ALTER TABLE primarykey1 ADD FOREIGN KEY (pk1col3, pk1col4) REFERENCES primarykey1 ON DELETE CASCADE

CREATE TABLE primarykey2 (pk2col1 INT, pk2col2 INT, pk2col3 INT, pk2col4 INT, PRIMARY KEY (pk2col1, pk2col2))

ALTER TABLE primarykey2 ADD FOREIGN KEY (pk2col3, pk2col4) REFERENCES primarykey2 ON DELETE CASCADE

CREATE TABLE foreignkey1 (fkcol1 INT, fkcol2 INT, fkcol3 INT, fkcol4 INT)

ALTER TABLE foreignkey1 ADD FOREIGN KEY (fkcol1, fkcol2) REFERENCES PRIMARYKEY1

ALTER TABLE foreignkey1 ADD FOREIGN KEY (fkcol3, fkcol4) REFERENCES PRIMARYKEY2

SELECT * FROM dbo.fSQLForeignKeys ('Demodata', 'primarykey%', 'foreignkey1')

Result Set (abbreviated for space considerations):

FKCOLUMN_NAME DELETE_RULE FK_NAME PK_NAME

============= =========== ========== ==========

fkcol1 1 FK_0fkcol1 PK_pk1col1

fkcol2 1 FK_0fkcol1 PK_pk1col1

fkcol3 1 FK_0fkcol3 PK_pk2col1

fkcol4 1 FK_0fkcol3 PK_pk2col1

4 rows were affected.