The stored procedure
GET_FOREIGN_KEYS returns the foreign keys of each view. Each row of the result represents a field of a foreign key.
This procedure is the opposite of GET_EXPORTED_KEYS.
A foreign key is a field (or collection of fields) of a view that uniquely identifies a row of another view or the same view. For example, if you have these views:
- Order: the primary key is “id”.
- Order_details: the view has a field “order_id” whose value is the id of the order that it belongs to.
To define a foreign key with them, you have to create an association between “order” and “order_details”:
- The mapping has to be between the field “order_id” of “order_details” and “id” of “order”.
- Select the option Referential constraint. Otherwise, it is not considered a foreign key.
- The end point “order” is the Principal and “player”, the Dependent.
- In the end point “order”, the cardinality is 1. This means that a row of “order_details” can only refer to one row of “order”.
The section Associations of the Administration Guide explains why they are useful and how to create them.
GET_FOREIGN_KEYS ( input_database_name : text , input_view_name : text )
null, the procedure returns the foreign keys of all the views of all the databases.
null, the procedure returns the foreign keys of all the views of that database.
The procedure returns these fields:
pk_database_name: name of database that the view of the primary key belongs to.
pk_view_name: name of the view of the primary key.
pk_column_name: name of the field that is part of the primary key.
pk_name: name of the primary key.
fk_database_name: name of database that the view of the foreign key belongs to.
fk_view_name: name of the view of the foreign key.
fk_column_name: name of the field that is part of the foreign key.
fk_name: name of the association.
The results of this procedure change depending on the privileges granted to the user that runs it. If the user is not an administrator user, consider the following:
- If the parameter
input_database_nameis not NULL, the procedure returns an error if the user does not have CONNECT privileges over this database.
- The procedure will only return information about the foreign keys of a view when the user has
EXECUTEprivileges over that view and the referenced view.
Let us say that you have three views like in the figure below:
The figure below is the configuration of the association between the views “player” and “game”:
SELECT pk_view_name, pk_column_name, pk_name, fk_view_name, fk_column_name, fk_name FROM get_exported_keys() WHERE input_database_name = 'example'
When you do not pass a value to the input parameter “input_view_name” or pass NULL, the result of this procedure is the same as
SELECT pk_view_name, pk_column_name, pk_name, fk_view_name, fk_column_name, fk_name FROM get_foreign_keys() WHERE input_database_name = 'example' and input_view_name='player'
The result is:
SELECT pk_view_name, pk_column_name, pk_name, fk_view_name, fk_column_name, fk_name FROM get_foreign_keys() WHERE input_database_name = 'example' and input_view_name='game'
This query does not return any row because there are not foreign keys that reference the view “game”.
See the example of the procedure GET_EXPORTED_KEYS to understand the difference between both procedures.