Forum Discussion

n_campbell's avatar
n_campbell
Regular Visitor
1 year ago

DatabaseMetadata.getExported or Imported keys does not return metadata for a warehouse/lakehouse

One or more tables were defined (alter table...) with primary, unique and foreign key constraints.

A connection is established to a warehouse or lakehouse end point.

When the DatabaseMetadata.getImported/ExportedKeys method is called for a specified table in a schema, no metadata (empty Resultset) is returned. Only DatabaseMetadata.getPrimaryKeys returns constraint metadata for a table.

Is this a known defect?

Changing method reference from primary to exported/imported will result in no metadata.

System.out.println("Get constraints");
int i = 0;
try (ResultSet rsFunctions = dbMeta.getPrimaryKeys("dqm_warehouse", "dbcert", "TCONS3");) {
while (rsFunctions.next()) {
System.out.println(rsFunctions.getString(1) + "." + rsFunctions.getString(2) + "."
+ rsFunctions.getString(3));
++i;
}
}
System.out.println("Constraints found " + i);

workspaces

1 Reply

  • tayloramy's avatar
    tayloramy
    Icon for Super User rankSuper User
    1 year ago

    Hi n_campbell,

    This behavior is expected in Microsoft Fabric today. You can define primary, unique, and foreign keys in a Fabric Warehouse or a Lakehouse’s SQL analytics endpoint using ALTER TABLE, but those constraints are not enforced (for example, foreign keys are always NOT ENFORCED). Because the constraints are informational rather than enforced relational constraints, they aren’t consistently surfaced through JDBC metadata for imported/exported keys.

    Two Microsoft docs that help explain the current state:

    • Table constraints in Fabric: Documents that PRIMARY KEY and UNIQUE must be NONCLUSTERED and NOT ENFORCED, and FOREIGN KEY is NOT ENFORCED-i.e., these are not enforced relational constraints like in a traditional SQL Server database. This is the core reason JDBC metadata for relationships may not appear.
    • Warehouse/Lakehouse limitations: Highlights special behaviors and limitations of the SQL analytics endpoint in Fabric (distinct from classic SQL Server), underscoring that schema/metadata support differs.

    Given the above, it’s normal that:

    • DatabaseMetaData.getPrimaryKeys(...) can return rows, since primary key definitions exist, even if not enforced.
    • getImportedKeys(...) and getExportedKeys(...) may return no rows against Fabric Warehouse/Lakehouse endpoints, because relationship metadata is not exposed via those JDBC metadata calls in this environment. (The JDBC methods' existence is documented by Microsoft, but Fabric's endpoints don't currently surface the FK relationships through them.)

    Workarounds

    • Query system catalog views directly to retrieve FK metadata (for example, sys.foreign_keys, sys.foreign_key_columns):
    SELECT 
    sch.name  AS SchemaName,
    t.name    AS TableName,
    fk.name   AS ForeignKeyName
FROM sys.foreign_keys fk
JOIN sys.tables t      ON fk.parent_object_id = t.object_id
JOIN sys.schemas sch   ON t.schema_id = sch.schema_id;

    So in short: your results align with Fabric’s current design-constraints can be declared but aren’t enforced, and FK relationship metadata isn’t surfaced via JDBC’s imported/exported keys on these endpoints. Use the system catalog views if you need to programmatically inspect relationships today.

    References

     

    If you found this helpful, consider giving some Kudos.
    If I answered your question or solved your problem, please mark this post as the solution.