Редагувати

Поділитися через

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. These cannot be created inline within a CREATE TABLE statement.

Table constraints

SQL analytics endpoint and Warehouse in Microsoft Fabric support these table constraints:

  • PRIMARY KEY is only supported when NONCLUSTERED and NOT ENFORCED are both used.
  • FOREIGN KEY is only supported when NOT ENFORCED is used.
  • UNIQUE constraint is only supported when NONCLUSTERED and NOT ENFORCED are both used.

For syntax, check ALTER TABLE.

Important

There are limitations with adding table constraints or columns when using Source Control with Warehouse.

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