约束（Constraint）

SQL约束用于规定表中的数据规则。如果一个用户试图在表中插入违反约束的数据，会抛出错误。约束分为以下几类：

• 非空约束
• 唯一约束
• 主键约束
• 检查约束
• 外键约束

--创建表添加非空约束示例。
gaussdb=# CREATE TABLE tbl_person1(
    id int not null,
    name varchar(50)
);
CREATE TABLE

--插入不符合约束的数据，触发约束报错。
gaussdb=# INSERT INTO tbl_person1 (id,name) VALUES (NULL,'Bob');
ERROR:  The null value in column "id" violates the not-null constraint.
DETAIL:  Failing row contains (null, Bob).

--删除表。
gaussdb=# DROP TABLE tbl_person1;
DROP TABLE

--创建表添加字段级唯一约束示例。
gaussdb=# CREATE TABLE tbl_person2(
    id int UNIQUE, 
    name varchar(50)
);
CREATE TABLE

--创建表添加表级唯一约束示例。
gaussdb=# CREATE TABLE tbl_person3(
    id int,
    name varchar(50),
    CONSTRAINT uq_person3_id UNIQUE(id)
);
CREATE TABLE

--插入重复的数据触发约束报错。
gaussdb=# INSERT INTO tbl_person2 VALUES (1,'Bob');
INSERT 0 1
gaussdb=# INSERT INTO tbl_person2 VALUES (1,'Bob');
ERROR:  Duplicate key value violates unique constraint "tbl_person2_id_key".
DETAIL:  Key (id)=(1) already exists.

--重复的NULL值不触发唯一约束。
gaussdb=# INSERT INTO tbl_person2 VALUES (null,'Bob');
INSERT 0 1
gaussdb=# INSERT INTO tbl_person2 VALUES (null,'Bob');
INSERT 0 1

--删除。
gaussdb=# DROP TABLE tbl_person2,tbl_person3;
DROP TABLE

--创建表添加字段级主键约束示例。
gaussdb=# CREATE TABLE tbl_person4(
    id int PRIMARY KEY,
    name varchar(50)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tbl_person4_pkey" for table "tbl_person4"
CREATE TABLE

--创建表添加表级主键约束示例。
gaussdb=# CREATE TABLE tbl_person5(
    id int,
    name varchar(50),
    CONSTRAINT pk_person5_id PRIMARY KEY(id)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pk_person5_id" for table "tbl_person5"
CREATE TABLE

--删除表。
gaussdb=# DROP TABLE tbl_person4,tbl_person5;
DROP TABLE

--创建表添加检查约束示例。
gaussdb=# CREATE TABLE tbl_person6(
    id int PRIMARY KEY,
    name varchar(50) NOT NULL,
    age int CHECK(age > 0 AND age < 200) NOT NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tbl_person6_pkey" for table "tbl_person6"
CREATE TABLE

--插入不符合约束的数据，触发约束报错。
gaussdb=# INSERT INTO tbl_person6 VALUES (1,'Bob',500);
ERROR:  New row in relation "tbl_person6" violates check constraint "tbl_person6_age_check".
DETAIL:  N/A

--删除表。
gaussdb=# DROP TABLE tbl_person6;
DROP TABLE

--创建部门表。
gaussdb=# CREATE TABLE works(
    w_id int primary key, --被引用的字段必须有主键约束或者唯一约束
    w_name varchar(50)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "works_pkey" for table "works"
CREATE TABLE


--创建员工表，w_id字段引用工作表的w_id。
gaussdb=# CREATE TABLE person(
    p_id int PRIMARY KEY,
    p_name varchar(50),
    p_age int CHECK(p_age > 0 and p_age < 200),
    w_id int,
    CONSTRAINT fk_person FOREIGN KEY (w_id) REFERENCES works(w_id)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "person_pkey" for table "person"
CREATE TABLE


--工作表中插入数据。
gaussdb=# INSERT INTO works VALUES (90010,'销售部');
INSERT 0 1
gaussdb=# INSERT INTO works VALUES (90020,'运维部');
INSERT 0 1
gaussdb=# INSERT INTO works VALUES (90030,'开发部');
INSERT 0 1

--员工表中插入正常数据。
gaussdb=# INSERT INTO person VALUES (1, '小红', 27, 90010);
INSERT 0 1

--员工表中插入不符合约束数据示例（works表中没有w_id为90040的数据）。
gaussdb=# INSERT INTO person VALUES (2, '小亮', 32, 90040);
ERROR:  insert or update on table "person" violates foreign key constraint "fk_person"
DETAIL:  Key (w_id)=(90040) is not present in table "works".

--如下情况可以在员工表的w_id字段上添加非空约束。
gaussdb=# INSERT INTO person VALUES (2, '小亮', 32, NULL);
INSERT 0 1

在定义外键约束时，可以通过ON关键字来指定当主表中数据（示例中works表）被删除时，如何处理子表中的数据（示例中的person表）。

• ON DELETE CASCADE：指定主表删除数据时级联删除子表中所有相关的记录。
• ON DELETE SET NULL：指定主表删除数据时将子表中相关的数据该字段设置为NULL。
• ON DELETE NO ACTION：指定主表删除数据时，如果子表中有相关的数据那么报错不做任何操作（默认情况）。

--默认情况下删除主表数据示例。
gaussdb=# DELETE FROM works WHERE w_id = 90010;
ERROR:  update or delete on table "works" violates foreign key constraint "fk_person" on table "person"
DETAIL:  Key (w_id)=(90010) is still referenced from table "person".

--ON DELETE SET NULL示例。
gaussdb=# CREATE TABLE person2(
    p_id int PRIMARY KEY,
    p_name varchar(50),
    p_age int CHECK(p_age > 0 and p_age < 200),
    w_id int,
    CONSTRAINT fk_person FOREIGN KEY (w_id) REFERENCES works(w_id) ON DELETE SET NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "person2_pkey" for table "person2"
CREATE TABLE


--插入数据。
gaussdb=# INSERT INTO person2 VALUES (3,'小明',24,90020);
INSERT 0 1

--查看表数据。
gaussdb=# SELECT * FROM person2;
 p_id | p_name | p_age | w_id  
------+--------+-------+-------
    3 | 小明   |    24 | 90020
(1 row)

--删除主表数据。
gaussdb=# DELETE FROM works WHERE w_id = 90020;
DELETE 1

--查看子表数据，w_id字段被改为NULL。
gaussdb=# SELECT * FROM person2;
 p_id | p_name | p_age | w_id 
------+--------+-------+------
    3 | 小明   |    24 |     
(1 row)

--删除表。
gaussdb=# DROP TABLE works,person,person2;
DROP TABLE