Forum Discussion
DatabaseMetadata.getExported/ImportedKeys does not return metadata for table in a lake/warehouse
One or more tables are defined (alter table add constraint ...) with primary, unique and foreign key constraints.
A connection is established to a lake/warehouse endpoint with the SQL Server JDBC driver.
When DatabaseMetadata.getPrimaryKeys is called for a specified table, metadata is returned.
When DatabaseMetadata.getExportedKey or getImportedKeys is called for a specified table, no metadata is returned.
Is this a known defect?
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);
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.
5 Replies
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.Hi n_campbell ,
Thank you for reaching out to the Microsoft Community Forum.
Hi tayloramy , Thank you for your prompt response.
Hi n_campbell , As mentioned by tayloramy , Yes, this behavior is a known limitation of the SQL Server JDBC driver when using, DatabaseMetaData.getExportedKeys() and getImportedKeys() especially in environments like lakehouses or warehouses.
1. The JDBC driver internally uses the sp_fkeys stored procedure to retrieve foreign key metadata. Unfortunately, sp_fkeys only returns limited information, and in some cases, it does not return any data for tables in certain environments like lakehouses or external tables. This affects both getExportedKeys() and getImportedKeys() calls, which rely on sp_fkeys.2. getPrimaryKeys() uses a different mechanism that queries system tables directly like sys.indexes and sys.key_constraints), which are more reliable and accessible even in lakehouse.
You can execute this query via JDBC and manually construct the metadata you need.
SELECT
fk.name AS ForeignKey,
tp.name AS ParentTable,
cp.name AS ParentColumn,
tr.name AS ReferencedTable,
cr.name AS ReferencedColumn
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tp ON fkc.parent_object_id = tp.object_id
INNER JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
INNER JOIN sys.tables tr ON fkc.referenced_object_id = tr.object_id
INNER JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id;Please refer below links.
Using database metadata - JDBC Driver for SQL Server | Microsoft Learn
SQLServerDatabaseMetaData Members - JDBC Driver for SQL Server | Microsoft Learn
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi n_campbell ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @n_campbell ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
