Fabric UX action does not include Primary/Foreign keys

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).

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!

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. 

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-transact-sql?view=sql-server-ver17 
2. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-key-constraints-transact-sql?view=sql-server-ver17 
3. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-columns-transact-sql?view=sql-server-ver17

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.