You can use QueryPad’s KEYS command to display information about any Foreign Keys for the tables in the database. A Foreign Key is a column in one table that has values that are used in a Primary Key in another table.
Each table has a Primary Key which is a column that contains values that uniquely identify each row in the table. For example, the DBObject table lists every database item, and so its Primary Key is the ObjectId column (which is hidden from view by default).
If a table has a column that contains values that are used in the Primary Key in another table, that column is referred to as a Foreign Key. The table that contains the Foreign Key is called the Foreign Table. (The table with Primary Key values that match the Foreign Key values is called the Primary Table).
Example:
In ClearSCADA, the CSCADAPackMAnalog table contains information about SCADAPack Modbus analog points. Each row represents a different point, and so the ObjectIds of the points are the Primary Key for the table. The table also contains an Outstation column. The values in the Outstation column are the unique identifiers of the outstations with which the analog points are associated.
If you want to see the CSCADAPackMAnalog table’s ID, Name and Outstation columns, display the CSCADAPackMAnalog table in ClearSCADA and edit its SQL query to:
SELECT
ID, ID, FULLNAME, OUTSTATIONID
FROM
CSCADAPACKMANALOG
ID is the Primary Key for the CSCADAPackMAnalog table. The Outstation column contains values that are used as the Primary Key in another table, and so this means the Outstation column is a Foreign Key.
The CAdvModbusOS table contains values for each individual Advanced Modbus outstation. Each row represents an individual outstation and so the ID is the Primary Key for the table.
The Outstation ID is the Primary Key of the CAdvModbusOS table.
The values in the ID column for the CAdvModbusOS table are also used in the Outstation column in the CSCADAPackMAnalog table. This means that there is a Primary Key-Foreign Key relationship between the two tables: CAdvModbusOS is a Primary Table in relation to the CSCADAPackMAnalog table (and the Outstation column of the CSCADAPackMAnalog table is the foreign key).
To use QueryPad to display information about foreign keys:
- Display QueryPad
- Connect to the appropriate system (see Connect QueryPad to a Database).
- Enter the ?KEYS command followed by the arguments that are appropriate to your requirements. The correct syntax is:
?KEYS <Primary Schema or %> <Primary Table> <Foreign Schema> <Foreign Table>
Where:
- <Primary Schema> is the name of the Primary schema (the schema that contains the Primary Table). You can also enter % instead of a Primary Schema if you wish (this will not affect what information is displayed in QueryPad). Do not include the angle brackets < >.
- <Primary Table> is the name of the Primary Table. You need to include the Primary Table for the KEYS command otherwise the KEYS command will not return the information you require.
- <Foreign Schema> is the name of the Foreign schema (the schema that contains the Foreign Table). Do not include the angle brackets < >.
- <Foreign Table> is the name of the Foreign Table. Do not include the angle brackets < >.
If you specify an exact Foreign Schema and Foreign Table, then QueryPad will only display information about the foreign keys that are part of that Foreign Schema and/or Table. However, if you specify a pattern for a Foreign Schema or Foreign Table (by using the % character), QueryPad will display information about every Foreign Key for the chosen Primary Table.
Example:
If you enter the query:
?KEYS % CAdvModbusOS % %
QueryPad will display Foreign Key information about every Foreign Key for the CAdvModbusOS table.
If you enter the query:
?KEYS % CAdvModbusOS % CDBPulse
QueryPad displays information about the CDBPulse table’s Foreign Keys to the CAdvModbusOS table.
The information that is listed in QueryPad includes:
- Row—The number of the row occupied by the Foreign Key in QueryPad’s result set (Foreign Key list). This row number is for the results displayed in QueryPad and is included to make it easier to read the QueryPad results. It does not relate to the rows in the database.
- PK_TABLE—The name of the Primary Table.
- PK_COLUMN—The name of the column that is the Primary Key for the Primary Table.
- FK_TABLE—The name of the Foreign Table.
- FK_COLUMN—The name of the column in the Foreign Table that is recognized a Foreign Key to the Primary Table.
- KEY_SEQ—A sequence number. This should be 1.
- PK_OWNER—The schema to which the Primary Table belongs.
- FK_OWNER—The schema to which the Foreign Table belongs.