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 [DemoDB].[PUBLIC].Products primary key, as well as the table that contains the associated foreign key:

String connectionString = "jdbc:snowflake:url=https://myaccount.region.snowflakecomputing.com;user=Admin;password=test123;Database=Northwind;Warehouse=TestWarehouse;Account=Tester1;";

Connection conn = DriverManager.getConnection(connectionString);
ResultSet rs = conn.getMetaData().getExportedKeys("DemoDB","PUBLIC","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 [DemoDB].[PUBLIC].Products table, as well as the table that contains the associated primary key:

String connectionString = "jdbc:snowflake:url=https://myaccount.region.snowflakecomputing.com;user=Admin;password=test123;Database=Northwind;Warehouse=TestWarehouse;Account=Tester1;";

Connection conn = DriverManager.getConnection(connectionString);
ResultSet rs = conn.getMetaData().getImportedKeys("DemoDB","PUBLIC","Products");
while(rs.next()){
  System.out.println(rs.getString("FKCOLUMN_NAME")+": "+rs.getString("PKTABLE_NAME"));
}

Both the getExportedKeys and getImportedKeys methods return the following columns:


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.

JDBC Driver for Snowflake

Foreign Keys