Fabric UX action does not include Primary/Foreign keys

[n_campbell]

When a table is selected and you right-mouse to select the action new-query->create table, the generated DDL does not include any PK, UK or FK constraints that were defined on the table.  Is this a design limitation or defect?

What action(s) will generate the full set of DDL for an object from the UX?

[n_campbell]

Given my prior comments, the customer facing documentation should be improved to enumerate those objects which can be defined but are not supported by the SQL Server JDBC driver implementation of applcable DatabaseMetadata methods. In turn, as and when those gaps are closed change the documentation and as applicable as of which version of the s/w (driver etc).

[v-kpoloju-msft · Accepted solution]

Hi @n_campbell,

Thanks again for the detailed follow-up and for sharing your perspective.

You are right. If certain database objects (PK, FK, UK, procedures, functions, etc.) can be defined but are not fully surfaced through the Microsoft JDBC driver’s DatabaseMetaData methods, this should be documented more clearly for customers.

At present, the JDBC support documentation (feature matrix and metadata overview) outlines general driver capabilities, but it does not yet provide a granular list of object types that are defined in SQL Server but not exposed through metadata APIs. I agree this could cause confusion for application developers who expect full JDBC compliance without needing to query system catalogs directly.

I will pass your feedback to the product team so they can review whether: The documentation can be updated to clearly enumerate current limitations and driver behaviour for metadata methods. Future driver releases can close these gaps and note the version where support is added.

In the meantime, the best approach is to continue referencing the feature matrix and the JDBC driver metadata docs to check current coverage, while raising any missing areas through feedback channels.
Refer these links:

https://learn.microsoft.com/en-us/sql/connect/driver-feature-matrix?view=sql-server-ver17 
https://learn.microsoft.com/en-us/sql/connect/jdbc/using-database-metadata?view=sql-server-ver17 

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @n_campbell,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @n_campbell,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @n_campbell,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[v-kpoloju-msft]

Hi @n_campbell,

Thank you for reaching out to the Microsoft fabric community forum and sharing the details and screenshot. Also, thanks to @suparnababu8, for his inputs on this thread.

I understand the concern that when you script out a table, the output only shows column definitions while keys and constraints (PK, FK, UK) are missing.

The default scripting option in SSMS/Fabric only generates the table structure, and constraints are not included unless you explicitly script them.

Here are two approaches mentioned below please go through them. Right-click your database in SSMS → Tasks → Generate Scripts. Choose your tables. In the Advanced Options, make sure:
• Script Primary Keys = True
• Script Foreign Keys = True
• Script Unique Keys = True
Run the wizard, and you will get the full CREATE TABLE script including constraints.

Refer these links:
1. https://learn.microsoft.com/en-us/ssms/scripting/generate-and-publish-scripts-wizard 
2. https://learn.microsoft.com/en-us/ssms/tutorials/scripting-ssms 
3. https://learn.microsoft.com/en-us/fabric/data-warehouse/table-constraints 

You can also query system catalog views (sys.tables, sys.key_constraints, sys.foreign_keys, etc.) to script DDL manually.

Refer these links:
1. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-foreign-keys-tra... 
2. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-key-constraints-... 
3. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-columns-transact...

Here’s a sample repro with data you can try in SSMS: 

CREATE TABLE Departments1 (
    DeptID INT PRIMARY KEY,
    DeptName NVARCHAR(50) UNIQUE
);

CREATE TABLE Employees1 (
    EmpID INT PRIMARY KEY,
    EmpName NVARCHAR(50),
    DeptID INT,
    CONSTRAINT FK_Employees_Departments FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

SELECT 
    sch.name AS SchemaName,
    t.name AS TableName,
    kc.name AS ConstraintName,
    kc.type_desc
FROM sys.key_constraints kc
JOIN sys.tables t ON kc.parent_object_id = t.object_id
JOIN sys.schemas sch ON t.schema_id = sch.schema_id
UNION
SELECT 
    sch.name,
    t.name,
    fk.name,
    'FOREIGN KEY'
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;

The Outcome in SSMS: 

This will return a list of all PK, UK, and FK constraints for your tables.

Please give this a try and let us know if it works, happy to help further if you have any doubts.

Thank you for using the Microsoft Fabric Community Forum.

[n_campbell]

An application introspects a database using JDBC and expects the implementations of several DatabaseMetadata methods (getExportedKeys, getImportedKeys, getProcedures, getFunctions, ...) to return metadata. 

It does not expect to generate custom SQL statements against the system catalog, it should be abstracted by the driver etc.

While the workarounds may be used by others, this is appears to be design limitation (or defect) which the documentation should include re Microsoft JDBC driver.  

[v-kpoloju-msft]

Hi @n_campbell,
Thanks a lot for clarifying. You are not writing catalog queries directly, but instead your application is using the standard DatabaseMetaData methods exposed by the Microsoft JDBC Driver (getExportedKeys, getImportedKeys, getProcedures, getFunctions, etc.).

The JDBC driver does implement many of these methods, but not all are fully supported for every SQL Server object type. In cases where metadata isn’t returned, it is a limitation of the driver’s current implementation rather than a SQL Server issue.

For reference, here are the official Microsoft documentation links mentioned below:
1. https://learn.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matr... 
2. https://learn.microsoft.com/en-us/sql/connect/driver-feature-matrix?view=sql-server-ver17 
3. https://learn.microsoft.com/en-us/sql/connect/jdbc/overview-of-the-jdbc-driver?view=sql-server-ver17 
4. https://learn.microsoft.com/en-us/sql/connect/jdbc/system-requirements-for-the-jdbc-driver?view=sql-... 
5. https://learn.microsoft.com/en-us/sql/connect/jdbc/using-database-metadata?view=sql-server-ver17 

In the meantime, while I understand your application expects abstraction at the driver level, the workaround remains to query system catalog views (like sys.foreign_keys, sys.procedures, sys.objects, etc.) when driver metadata APIs don’t provide the needed results.

Please give this a try and let us know if it works, happy to help further if you have any doubts.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @n_campbell,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @n_campbell,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[n_campbell]

Should this be a design constraint, then it would help if this were included in the Microsoft docs re what is not returned via an API such as JDBC. They indicate as such re non-simple types, where the driver simply does not return those columns in DatabaseMetadata.getColumns. 

[suparnababu8]

Hi @n_campbell 

I dont't think it's a defect, it's by design. Just give a try in Visual studio or SSMS.

Please let me knwo if it helps you

Thank you!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!