Primary keys, foreign keys, and unique keys in Warehouse in Microsoft Fabric

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

Learn about table constraints in SQL analytics endpoint and Warehouse in Microsoft Fabric, including the primary key, foreign keys, and unique keys.

Important

To add or remove primary key, foreign key, or unique constraints, use ALTER TABLE. You can't create these constraints inline within a CREATE TABLE statement.

Table constraints

SQL analytics endpoint and Warehouse in Microsoft Fabric support adding these table constraints to existing tables:

  • PRIMARY KEY is only supported when you use NONCLUSTERED and NOT ENFORCED.
  • FOREIGN KEY is only supported when you use NOT ENFORCED.
  • UNIQUE constraint is only supported when you use NONCLUSTERED and NOT ENFORCED.

For syntax, check ALTER TABLE.

  • SQL analytics endpoint and Warehouse don't support default constraints at this time.
  • For more information on tables, see Tables in Fabric Data Warehouse.

Important

There are limitations when adding table constraints or columns by using Git Integration for Fabric Warehouse Development.

Examples

Create a Microsoft Fabric Warehouse table with a primary key:

CREATE TABLE PrimaryKeyTable (c1 INT NOT NULL, c2 INT);

ALTER TABLE PrimaryKeyTable ADD CONSTRAINT PK_PrimaryKeyTable PRIMARY KEY NONCLUSTERED (c1) NOT ENFORCED;

Create a Microsoft Fabric Warehouse table with a unique constraint:

CREATE TABLE UniqueConstraintTable (c1 INT NOT NULL, c2 INT);

ALTER TABLE UniqueConstraintTable ADD CONSTRAINT UK_UniqueConstraintTablec1 UNIQUE NONCLUSTERED (c1) NOT ENFORCED;

Create a Microsoft Fabric Warehouse table with a foreign key:

CREATE TABLE ForeignKeyReferenceTable (c1 INT NOT NULL);

ALTER TABLE ForeignKeyReferenceTable ADD CONSTRAINT PK_ForeignKeyReferenceTable PRIMARY KEY NONCLUSTERED (c1) NOT ENFORCED;

CREATE TABLE ForeignKeyTable (c1 INT NOT NULL, c2 INT);

ALTER TABLE ForeignKeyTable ADD CONSTRAINT FK_ForeignKeyTablec1 FOREIGN KEY (c1) REFERENCES ForeignKeyReferenceTable (c1) NOT ENFORCED;

Related content

  • Last updated on