Catalogue Command (ODBC Only)
Returns meta data about foreign keys that link tables.
foreignkeys PKDATABASE PKSCHEMA PKTABLE FKDATABASE FKSCHEMA FKTABLE
PKDATABASE
Primary key table catalog/database name.
PKSCHEMA
Primary key table schema name.
PKTABLE
Primary key table name of the table to be processed.
FKDATABASE
Foreign key table catalog/database name.
FKSCHEMA
Foreign key table schema name.
FKTABLE
Foreign key table name of the table to be processed.
Description
Use this command to obtain information about the key columns that are used to link two tables. The application must have been logged into a data source using the login command, otherwise the following error message will be displayed:
Error: Not connected to database
Certain database management systems do not support different "Database" (or Catalogue) or "Schema" (or Table Owner), if so, a null string needs to be passed as the first and/or second parameter using the syntax '' (i.e. two single quotes).
If the parameters are valid, a result set will be returned containing one row as there must be only a single key column that connects two tables.The row contains a number of fields, the actual number may increase, dependent of the ODBC version, but will always start with the following:
Column Name |
Column Number |
Comment |
PKTABLE_CAT |
1 |
Primary key table catalog name; NULL if not applicable to the data source. |
PKTABLE_SCHEM |
2 |
Primary key table schema name; NULL if not applicable to the data source. |
PKTABLE_NAME |
3 |
Primary key table name. |
PKCOLUMN_NAME |
4 |
Primary key column name. The driver returns an empty string for a column that does not have a name. |
FKTABLE_CAT |
5 |
Foreign key table catalog name; NULL if not applicable to the data source. |
FKTABLE_SCHEM |
6 |
Foreign key table schema name; NULL if not applicable to the data source. |
FKTABLE_NAME |
7 |
Foreign key table name. |
FKCOLUMN_NAME |
8 |
Foreign key column name. The driver returns an empty string for a column that does not have a name. |
KEY_SEQ |
9 |
Column sequence number in key (starting with 1). |
UPDATE_RULE |
10 |
Action to be applied to the foreign key when the SQL operation is UPDATE. Can have one of the following values. (The referenced table is the table that has the primary key; the referencing table is the table that has the foreign key.) SQL_CASCADE (i.e. 0): When the primary key of the referenced table is updated, the foreign key of the referencing table is also updated. SQL_NO_ACTION (i.e. 3): If an update of the primary key of the referenced table would cause a "dangling reference" in the referencing table (that is, rows in the referencing table would have no counterparts in the referenced table), then the update is rejected. If an update of the foreign key of the referencing table would introduce a value that does not exist as a value of the primary key of the referenced table, then the update is rejected. (This action is the same as the SQL_RESTRICT (i.e. 1) action in ODBC 2.x.) SQL_SET_NULL (i.e. 2): When one or more rows in the referenced table are updated such that one or more components of the primary key are changed, the components of the foreign key in the referencing table that correspond to the changed components of the primary key are set to NULL in all matching rows of the referencing table. SQL_SET_DEFAULT (i.e. 4): When one or more rows in the referenced table are updated such that one or more components of the primary key are changed, the components of the foreign key in the referencing table that correspond to the changed components of the primary key are set to the applicable default values in all matching rows of the referencing table. NULL if not applicable to the data source. |
DELETE_RULE |
11 |
Action to be applied to the foreign key when the SQL operation is DELETE. (see. Update Rule comment for values.) |
FK_NAME |
12 |
Foreign key name. NULL if not applicable to the data source. |
PK_NAME |
13 |
Primary key name. NULL if not applicable to the data source. |
DEFERRABILITY |
14 |
SQL_INITIALLY_DEFERRED (i.e. 5), |
If the parameters are invalid, then an error message "ERROR: Failed to obtain foreign key information" will be returned.
Example
subroutine GetJoinClause(string: TableOwner_SV, string: Table1_SV, string: Table2_SV, string: Clause_SV) is
string Column1_LSV
Column2_LSV
begin guarded
response to start
send "foreignkeys '' " TableOwner_SV " " Table1_SV " '' " TableOwner_SV " " Table2_SV "\n" to SQL
response to "SQL>" from SQL
leave block
response to line from SQL
extract from input
skip by "~" # Catalog
skip by "~" # Schema
skip by "~" # Table
take to "~" Column1_LSV
skip by "~"
skip by "~" # Catalog
skip by "~" # Schema
skip by "~" # Table
take to "~" Column2_LSV
copy "where " Table1_SV "." Column1_LSV " = " Table2_SV "." Column2_LSV to Clause_SV
end
