Foreign Keys
You can obtain foreign key information by executing the getExportedKeys or getImportedKeys methods:
-
Call the getExportedKeys method to retrieve information about all the foreign keys that the specified table exports.
The following example outputs each foreign key that references the "SampleDB"."Sample".Products primary key, as well as the table that contains the associated foreign key:
String connectionString = "jdbc:azuresynapse:user=myuser;password=mypassword;Server=localhost;Database=Northwind;"; Connection conn = DriverManager.getConnection(connectionString); ResultSet rs = conn.getMetaData().getExportedKeys("SampleDB","Sample","Products"); while(rs.next()){ System.out.println(rs.getString("FKCOLUMN_NAME")+": "+rs.getString("FKTABLE_NAME")); }
-
Call the getImportedKeys method to return information about all the primary keys that the specified table imports.
The following example outputs each foreign key column that is imported by the "SampleDB"."Sample".Products table, as well as the table that contains the associated primary key:
String connectionString = "jdbc:azuresynapse:user=myuser;password=mypassword;Server=localhost;Database=Northwind;"; Connection conn = DriverManager.getConnection(connectionString); ResultSet rs = conn.getMetaData().getImportedKeys("SampleDB","Sample","Products"); while(rs.next()){ System.out.println(rs.getString("FKCOLUMN_NAME")+": "+rs.getString("PKTABLE_NAME")); }
Column Name | Data Type | Description |
PK_TABLE_CAT | String | The catalog of the table that contains the primary key. |
PKTABLE_SCHEM | String | The schema of the table that contains the primary key. |
PKTABLE_NAME | String | The name of the table that contains the primary key. |
PKCOLUMN_NAME | String | The column name of the primary key. |
FKTABLE_CAT | String | The catalog of the table that contains the foreign key. |
FKTABLE_SCHEM | String | The schema of the table that contains the foreign key. |
FKTABLE_NAME | String | The name of the table that contains the foreign key. |
FKCOLUMN_NAME | String | The column name of the foreign key. |
KEY_SEQ | short | The sequence number, or column index starting from 1, within the foreign key. |
UPDATE_RULE | short | The rule specifying the action when the primary key is updated. |
DELETE_RULE | short | The rule specifying the action when the primary key is deleted. |
FK_NAME | String | The foreign key name or null. |
PK_NAME | String | The primary key name. |
DEFERRABILITY | short | Whether the evaluation of foreign key constraints can be deferred until commit. |
The following table shows the rules for the UPDATE_RULE and DELETE_RULE columns. The rules are specified with the following constants defined in the DatabaseMetaData class:
Name | Value | Description |
importedKeyNoAction | 3 | Do not update the primary key if imported. |
importedKeyCascade | 0 | Update the imported key to match the primary key. |
importedKeySetNull | 2 | Change the imported key to "NULL" if the primary key has been updated. |
importedKeySetDefault | 4 | Change the imported key to default values if the primary key has been updated. |
importedKeyRestrict | 1 | This rule is the same as importedNoAction (provides backwards compatibility with ODBC 2.x) |
The following table shows the possible values for the DEFERRABILITY column. These values are specified with the following constants defined in the DatabaseMetaData class:
Name | Value | Description |
importedKeyInitiallyDeferred | 5 | Indicates whether the foreign key is initially deferred. |
importedKeyInitiallyImmediate | 6 | Indicates whether the foreign key is initially immediate. |
importedKeyNotDeferrable. | 7 | Indicates whether the foreign key is not deferrable. |
See SQL-92 for more information on the preceding values.