openGauss学习笔记-26 openGauss 高级数据管理-约束

openGauss学习笔记-26 openGauss 高级数据管理-约束

约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

openGauss中常用的约束如下：

• NOT NULL：指示某列不能存储NULL值。
• UNIQUE：确保某列的值都是唯一的。
• PRIMARY KEY：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY： 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK： 保证列中的值符合指定的条件。

26.1 NOT NULL约束

创建表时，如果不指定约束，默认值为NULL，即允许列插入空值。如果您不想某列存在NULL值，那么需要在该列上定义NOT NULL约束，指定在该列上的值不允许存在NULL值。插入数据时，如果该列存在NULL值，则会报错，插入失败。

NULL与没有数据是不一样的，它代表着未知的数据。

例如，创建表staff，共有5个字段，其中NAME，ID设置不接受空值。

openGauss=# CREATE TABLE staff(ID             INT      NOT NULL,NAME           char(8)    NOT NULL,AGE            INT     ,ADDRESS        CHAR(50),SALARY         REAL
);

给表staff插入数据。当ID字段插入空值时，数据库返回报错。

openGauss=# INSERT INTO staff  VALUES (1,'lily',28);
INSERT 0 1
openGauss=# INSERT INTO staff (NAME,AGE) VALUES ('JUCE',28);
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, JUCE    , 28, null, null).

26.2 UNIQUE约束

UNIQUE约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一。

对于唯一约束，NULL被认为是互不相等的。

例如，创建表staff1，表包含5个字段，其中AGE设置为UNIQUE，因此不能添加两条有相同年龄的记录。

openGauss=# CREATE TABLE staff1(ID             INT      NOT NULL,NAME           char(8)    NOT NULL,AGE            INT   NOT NULL  UNIQUE  ,ADDRESS        CHAR(50),SALARY         REAL
);

给表staff1表插入数据。当字段AGE插入两条一样的数据时，数据库返回报错。

openGauss=# INSERT INTO staff1  VALUES (1,'lily',28);
INSERT 0 1
openGauss=# INSERT INTO staff1 VALUES (2, 'JUCE',28);
ERROR:  duplicate key value violates unique constraint "staff1_age_key"
DETAIL:  Key (age)=(28) already exists.

26.3 PRIMARY KEY

PRIMARY KEY为主键，是数据表中每一条记录的唯一标识。主键约束声明表中的一个或者多个字段只能包含唯一的非NULL值。

主键是非空约束和唯一约束的组合。一个表只能声明一个主键。

例如，创建表staff2，其中ID为主键。

openGauss=# CREATE TABLE staff2(ID             INT     PRIMARY KEY     ,NAME           TEXT    NOT NULL,AGE            INT     NOT NULL,ADDRESS        CHAR(50),SALARY         REAL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "staff2_pkey" for table "staff2"
CREATE TABLE

26.4 FOREIGN KEY

FOREIGN KEY即外键约束，指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。通常一个表中的FOREIGN KEY指向另一个表中的 UNIQUE KEY（唯一约束的键），即维护了两个相关表之间的引用完整性。

例如，创建表staff3，包含5个字段。

openGauss=# CREATE TABLE staff3(ID             INT    PRIMARY KEY  NOT NULL,NAME           TEXT    NOT NULL,AGE            INT     NOT NULL,ADDRESS        CHAR(50),SALARY         REAL
);

创建一张DEPARTMENT表，并添加3个字段，其中EMP_ID为外键，参照staff3的ID字段：

openGauss=# CREATE TABLE DEPARTMENT(ID INT PRIMARY KEY      NOT NULL,DEPT           CHAR(50) NOT NULL,EMP_ID         INT      references staff3(ID)
);

FOREIGN Key在MySQL兼容性下，外键可以关联非唯一性索引。即一个表中的FOREIGN Key指向另一个表中的 Non-unique KEY（非唯一约束的键）。 注： 如果在MySQL兼容性下，定义外键指定ON UPDATE | DELETE CASCADE时，在非唯一性索引中，非唯一索引字段存在多个元组时，只要dml其中一行数据，则会触发外键表里关联的字段全部修改。但如果字段为NULL时，则不触发外键关联的字段做对应的修改。

MySQL兼容性需要安装dolphin插件才可生效。

openGauss=# create table t1(id int, name varchar);
CREATE TABLE
openGauss=# create table t2(id int, a_id int);
CREATE TABLE
-- create non-unique index on table t1.
openGauss=# create index a_index_1 on t1(id);
CREATE INDEX
-- create foreign key on non-unique index
openGauss=# alter table t2 add constraint t2_fk foreign key (a_id) references t1(id);
ALTER TABLE
openGauss=# \d t1Table "public.t1"Column |       Type        | Modifiers 
--------+-------------------+-----------id     | integer           | name   | character varying | 
Indexes:"a_index_1" btree (id) TABLESPACE pg_default
Referenced by:TABLE "t2" CONSTRAINT "t2_fk" FOREIGN KEY (a_id) REFERENCES t1(id)openGauss=# \d t2Table "public.t2"Column |  Type   | Modifiers 
--------+---------+-----------id     | integer | a_id   | integer | 
Foreign-key constraints:"t2_fk" FOREIGN KEY (a_id) REFERENCES t1(id)openGauss=# insert into t1 values(1,'a'),(2,'b');
INSERT 0 2
openGauss=# select * from t1;id | name 
----+------1 | a2 | b
(2 rows)openGauss=# insert into t2 values(1,1);
INSERT 0 1
openGauss=# select * from t2;id | a_id 
----+------1 |    1
(1 row)openGauss=# insert into t2 values(1,3);
INSERT 0 1
ERROR:  insert or update on table "t2" violates foreign key constraint "t2_fk"
DETAIL:  Key (a_id)=(3) is not present in table "t1".
openGauss=# select * from t2;id | a_id 
----+------1 |    1
(1 row)openGauss=# alter table t2 drop constraint t2_fk;
ALTER TABLE
openGauss=# alter table t2 add constraint t2_fk foreign key (a_id) references t1(id) on update cascade;
ALTER TABLE
openGauss=# select * from t1;id | name 
----+------1 | a2 | b
(2 rows)openGauss=# insert into t1 values(1,'s');
INSERT 0 1
openGauss=# select * from t1;id | name 
----+------1 | a2 | b1 | s
(3 rows)openGauss=# insert into t2 values(2,1);
INSERT 0 1
openGauss=# select * from t2;id | a_id 
----+------1 |    12 |    1
(2 rows)openGauss=# update t1 set id = 11 where name = 'a';
UPDATE 1
openGauss=# select * from t1;id | name 
----+------2 | b1 | s11 | a
(3 rows)openGauss=# select * from t2;id | a_id 
----+------1 |   112 |   11
(2 rows)openGauss=# update t1 set id =1 where name = 'a';
UPDATE 1
openGauss=# alter table t2 drop constraint t2_fk;
ALTER TABLE
openGauss=# alter table t2 add constraint t2_fk foreign key (a_id) references t1(id) on delete cascade;
ALTER TABLE
openGauss=# select * from t1;id | name 
----+------2 | b1 | s1 | a
(3 rows)openGauss=# select * from t2;id | a_id 
----+------1 |    12 |    1
(2 rows)openGauss=# delete from t1 where name = 's';
DELETE 1
openGauss=# select * from t1;id | name 
----+------2 | b1 | a
(2 rows)openGauss=# select * from t2;id | a_id 
----+------
(0 rows)

26.5 CHECK约束

CHECK约束声明一个布尔表达式，每次要插入的新行或者要更新的行的新值必须使表达式结果为真或未知才能成功，否则会抛出一个异常并且不会修改数据库。

声明为字段约束的检查约束应该只引用该字段的数值，而在表约束里出现的表达式可以引用多个字段。expression表达式中，如果存在“<>NULL”或“！=NULL”，这种写法是无效的，需要写成“is NOT NULL”。

例如，创建表staff4，对字段SALARY新增CHECK约束，确保插入此列数值大于0。

openGauss=# CREATE TABLE staff4(ID INT PRIMARY KEY     NOT NULL,NAME           TEXT    NOT NULL,AGE            INT     NOT NULL,ADDRESS        CHAR(50),SALARY         REAL    CHECK(SALARY > 0)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "staff4_pkey" for table "staff4"
CREATE TABLE

给表staff4插入数据。当字段SALARY插入数据不大于0时，数据库返回报错。

openGauss=# INSERT INTO staff4(ID,NAME,AGE,SALARY) VALUES (2, 'JUCE',16,0);
ERROR:  new row for relation "staff4" violates check constraint "staff4_salary_check"
DETAIL:  N/A

👍 点赞，你的认可是我创作的动力！

⭐️ 收藏，你的青睐是我努力的方向！

✏️ 评论，你的意见是我进步的财富！