JDBC Driver for SQL Server

Build 20.0.7587

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

    String connectionString = "jdbc:sql:user=myuser;password=mypassword;Server=localhost;Database=Northwind;";
    
    Connection conn = DriverManager.getConnection(connectionString);
    ResultSet rs = conn.getMetaData().getExportedKeys(null, null, "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 Products table, as well as the table that contains the associated primary key:

    String connectionString = "jdbc:sql:user=myuser;password=mypassword;Server=localhost;Database=Northwind;";
    
    Connection conn = DriverManager.getConnection(connectionString);
    ResultSet rs = conn.getMetaData().getImportedKeys(null, null, "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 NameData TypeDescription
PK_TABLE_CATStringThe catalog of the table that contains the primary key.
PKTABLE_SCHEMStringThe schema of the table that contains the primary key.
PKTABLE_NAMEStringThe name of the table that contains the primary key.
PKCOLUMN_NAMEStringThe column name of the primary key.
FKTABLE_CATStringThe catalog of the table that contains the foreign key.
FKTABLE_SCHEMStringThe schema of the table that contains the foreign key.
FKTABLE_NAMEStringThe name of the table that contains the foreign key.
FKCOLUMN_NAMEStringThe column name of the foreign key.
KEY_SEQshortThe sequence number, or column index starting from 1, within the foreign key.
UPDATE_RULEshortThe rule specifying the action when the primary key is updated.
DELETE_RULEshortThe rule specifying the action when the primary key is deleted.
FK_NAMEStringThe foreign key name or null.
PK_NAMEStringThe primary key name.
DEFERRABILITYshortWhether 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:

NameValueDescription
importedKeyNoAction3Do not update the primary key if imported.
importedKeyCascade0Update the imported key to match the primary key.
importedKeySetNull2Change the imported key to "NULL" if the primary key has been updated.
importedKeySetDefault4Change the imported key to default values if the primary key has been updated.
importedKeyRestrict1This 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:

NameValueDescription
importedKeyInitiallyDeferred5Indicates whether the foreign key is initially deferred.
importedKeyInitiallyImmediate6Indicates whether the foreign key is initially immediate.
importedKeyNotDeferrable.7Indicates whether the foreign key is not deferrable.

See SQL-92 for more information on the preceding values.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587