Synapse SQL 池中的主键、外键和唯一键Primary key, foreign key, and unique key in Synapse SQL pool

了解 Synapse SQL 池中的表约束,包括主键、外键和唯一键。Learn about table constraints in Synapse SQL pool, including primary key, foreign key, and unique key.

表约束Table constraints

Synapse SQL 池支持以下表约束:Synapse SQL pool supports these table constraints:

  • 仅当同时使用 NONCLUSTERED 和 NOT ENFORCED 时才支持 PRIMARY KEY。PRIMARY KEY is only supported when NONCLUSTERED and NOT ENFORCED are both used.
  • 仅在使用 NOT ENFORCED 时才支持 UNIQUE 约束。UNIQUE constraint is only supported with NOT ENFORCED is used.

有关语法,请查看 ALTER TABLECREATE TABLEFor syntax, check ALTER TABLE and CREATE TABLE.

Synapse SQL 池不支持外键约束。FOREIGN KEY constraint is not supported in Synapse SQL pool.

备注Remarks

有了主键和/或唯一键,Synapse SQL 池引擎就可以为查询生成最佳执行计划。Having primary key and/or unique key allows Synapse SQL pool engine to generate an optimal execution plan for a query. 主键列或唯一约束列中的所有值都应独一无二。All values in a primary key column or a unique constraint column should be unique.

在 Synapse SQL 池中创建具有主键或唯一约束的表以后,用户需确保这些列中的所有值都是独一无二的。After creating a table with primary key or unique constraint in Synapse SQL pool, users need to make sure all values in those columns are unique. 违反这一点可能会导致查询返回不准确的结果。A violation of that may cause the query to return inaccurate result. 此示例表明,如果主键或唯一约束列包含重复值,查询可能返回不准确的结果。This example shows how a query may return inaccurate result if the primary key or unique constraint column includes duplicate values.

 -- Create table t1
CREATE TABLE t1 (a1 INT NOT NULL, b1 INT) WITH (DISTRIBUTION = ROUND_ROBIN)

-- Insert values to table t1 with duplicate values in column a1.
INSERT INTO t1 VALUES (1, 100)
INSERT INTO t1 VALUES (1, 1000)
INSERT INTO t1 VALUES (2, 200)
INSERT INTO t1 VALUES (3, 300)
INSERT INTO t1 VALUES (4, 400)

-- Run this query.  No primary key or unique constraint.  4 rows returned. Correct result.
SELECT a1, COUNT(*) AS total FROM t1 GROUP BY a1

/*
a1          total
----------- -----------
1           2
2           1
3           1
4           1

(4 rows affected)
*/

-- Add unique constraint
ALTER TABLE t1 ADD CONSTRAINT unique_t1_a1 unique (a1) NOT ENFORCED

-- Re-run this query.  5 rows returned.  Incorrect result.
SELECT a1, count(*) AS total FROM t1 GROUP BY a1

/*
a1          total
----------- -----------
2           1
4           1
1           1
3           1
1           1

(5 rows affected)
*/

-- Drop unique constraint.
ALTER TABLE t1 DROP CONSTRAINT unique_t1_a1

-- Add primary key constraint
ALTER TABLE t1 add CONSTRAINT PK_t1_a1 PRIMARY KEY NONCLUSTERED (a1) NOT ENFORCED

-- Re-run this query.  5 rows returned.  Incorrect result.
SELECT a1, COUNT(*) AS total FROM t1 GROUP BY a1

/*
a1          total
----------- -----------
2           1
4           1
1           1
3           1
1           1

(5 rows affected)
*/

-- Manually fix the duplicate values in a1
UPDATE t1 SET a1 = 0 WHERE b1 = 1000

-- Verify no duplicate values in column a1 
SELECT * FROM t1

/*
a1          b1
----------- -----------
2           200
3           300
4           400
0           1000
1           100

(5 rows affected)
*/

-- Add unique constraint
ALTER TABLE t1 add CONSTRAINT unique_t1_a1 UNIQUE (a1) NOT ENFORCED  

-- Re-run this query.  5 rows returned.  Correct result.
SELECT a1, COUNT(*) as total FROM t1 GROUP BY a1

/*
a1          total
----------- -----------
2           1
3           1
4           1
0           1
1           1

(5 rows affected)
*/

-- Drop unique constraint.
ALTER TABLE t1 DROP CONSTRAINT unique_t1_a1

-- Add primary key contraint
ALTER TABLE t1 ADD CONSTRAINT PK_t1_a1 PRIMARY KEY NONCLUSTERED (a1) NOT ENFORCED

-- Re-run this query.  5 rows returned.  Correct result.
SELECT a1, COUNT(*) AS total FROM t1 GROUP BY a1

/*
a1          total
----------- -----------
2           1
3           1
4           1
0           1
1           1

(5 rows affected)
*/

示例Examples

创建具有主键的 Synapse SQL 池表:Create a Synapse SQL pool table with a primary key:

CREATE TABLE mytable (c1 INT PRIMARY KEY NONCLUSTERED NOT ENFORCED, c2 INT);

创建具有唯一约束的 Synapse SQL 池表:Create a Synapse SQL pool table with a unique constraint:

CREATE TABLE t6 (c1 INT UNIQUE NOT ENFORCED, c2 INT);

后续步骤Next steps

为 Synapse SQL 池创建表后,下一步是将数据加载到该表中。After creating the tables for your Synapse SQL pool, the next step is to load data into the table. 有关进行加载的教程,请参阅将数据加载到 Synapse SQL 池For a loading tutorial, see Loading data to Synapse SQL pool.