Question

5.5. 约束

5.5.1. 检查约束

例子1：
有这样一个需求，在很多电子商务网站上都要对用户进行诚信评估，诚信分为五级（五个星），这样就要求某字段插入的数据0，1，2，3，4，5。“0”表示该用户没用评估。
-- ======================================================
--  'trust'
-- ======================================================
Create table "trust"
(
       "id" Serial NOT NULL UNIQUE,
       "uid" integer NOT NULL Default 0,
       "rate" Varchar(20) Default '0' Check (rate in ('0','1','2','3','4','5')),
      primary key ("id")
);
Alter table "trust" add  foreign key ("uid") references "user" ("id") on update restrict on delete restrict;
member=> Insert into trust (uid) values((select id from "user" where userid='netkiller'));
INSERT 111237 1
member=> Insert into trust (uid,rate) values((select id from "user" where userid='netkiller'),5);
INSERT 111220 1
member=> Insert into trust (uid,rate) values((select id from "user" where userid='netkiller'),2);
INSERT 111236 1
member=> Insert into trust (uid,rate) values((select id from "user" where userid='netkiller'),6);
ERROR:  ExecInsert: rejected due to CHECK constraint "trust_rate" on "trust"
member=> Insert into trust (uid,rate) values((select id from "user" where userid='netkiller'),10);
ERROR:  ExecInsert: rejected due to CHECK constraint "trust_rate" on "trust"
member=> select * from trust;
 id | uid | rate
----+-----+------
  1 | 257 | 2
  4 | 257 | 0
  5 | 257 | 5
(3 rows)

当插入数据不在枚举的范围内，提示ERROR:  ExecInsert: rejected due to CHECK constraint "trust_rate" on "trust"。

例子2：
检查某字段，不允许出现数值，使用not in 来完成。
DROP TABLE ctoc.bid CASCADE;
CREATE TABLE ctoc.bid(
       id Serial NOT NULL UNIQUE,
    salesroom_id integer DEFAULT '1' NOT NULL,  -- foreign key
    bidder integer  DEFAULT '1' NOT NULL,  -- foreign key
       price       numeric(8,2) DEFAULT '0.00' NOT NULL,
       quantity Integer DEFAULT '1' NOT NULL Check (quantity not in ('0')),
    created  timestamp DEFAULT current_timestamp::timestamp (0) without time zone,
    status boolean DEFAULT true,
    PRIMARY KEY (id),
    FOREIGN KEY (salesroom_id) REFERENCES ctoc.salesroom (id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (bidder) REFERENCES person (uid) ON UPDATE CASCADE ON DELETE CASCADE
);
netkiller=> insert into ctoc.bid(salesroom_id,bidder,price,quantity,status) values(1,8,100,0,true);
ERROR:  ExecInsert: rejected due to CHECK constraint "bid_quantity" on "bid"

5.5.2. 非空约束

显示的有note字段为空的记录：
member=> select * from vregion where note is null;

5.5.3. 唯一约束

单字段约束

这个例子对groupname字段做唯一操作。
-- ======================================================
--  'group'
-- ======================================================
Create table "group"
(
       "id" Serial NOT NULL UNIQUE,
       "groupname" Varchar(20) NOT NULL,
       "description" Varchar(255),
       UNIQUE (groupname),
      PRIMARY KEY ("id")
);
测试：
member=> insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
INSERT 110497 1
member=> insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
ERROR:  Cannot insert a duplicate key into unique index group_groupname_key
member=> insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
ERROR:  Cannot insert a duplicate key into unique index group_groupname_key
Psql 命令行返回ERROR:  Cannot insert a duplicate key into unique index group_groupname_key唯一约束成功。

多个字段组合约束

UNIQUE (rid,uid)中有多个参数，是对rid,uid组合约束。
例如：
1，1
1，2
是正确的

1，1
2，1
也是正确的

2，1
1，1
2，2
1，2
1，1
不正确的不允许插入数据“1，1”，数据“1，1”出现了两次，所以要同时满足rid,uid两个条件。

三个字段以上组合：
1,1,1
1,1,2
1,2,1
2,1,2
2,1,1
2,2,2
正确可以插入数据

1，2，1
2，1，2
2，2，1
1，1，2
2，2，1
“2，2，1”，“2，2，1”出现两次，违反约束条件，所以不能再次插入数据“2，2，1”。

-- ======================================================
--  'rolemember'
-- ======================================================
-- drop table rolemember CASCADE ;
Create table "rolemember"
(
       "id" Serial NOT NULL UNIQUE,
       "rid" integer NOT NULL Default 0,
       "uid" integer NOT NULL Default 0,
       UNIQUE (rid,uid),
 primary key ("id")
);
member=> insert into rolemember(rid,uid) values((select id from role where rolename ='System'),(select id from vuser where userid='sysop'));
INSERT 110954 1
member=> insert into rolemember(rid,uid) values((select id from role where rolename ='System'),(select id from vuser where userid='sysop'));
ERROR:  Cannot insert a duplicate key into unique index rolemember_rid_key
member=> insert into rolemember(rid,uid) values((select id from role where rolename ='System'),(select id from vuser where userid='admin'));
ERROR:  More than one tuple returned by a subselect used as an expression.
member=> insert into rolemember(rid,uid) values((select id from role where rolename ='System'),(select id from vuser where userid='test'));
INSERT 110956 1
member=> insert into rolemember(rid,uid) values((select id from role where rolename ='System'),(select id from vuser where userid='test'));
ERROR:  Cannot insert a duplicate key into unique index rolemember_rid_key

唯一约束的注意事项

这个例子对groupname字段做唯一操作。
-- ======================================================
--  'group'
-- ======================================================
Create table "group"
(
       "id" Serial NOT NULL UNIQUE,
       "groupname" Varchar(20) NOT NULL,
       "description" Varchar(255),
       UNIQUE (id,groupname),
      PRIMARY KEY ("id")
);
仔细看这个例子没有错。
运行结果：
postgres=# Create table "group"
postgres-# (
postgres(# "id" Serial NOT NULL UNIQUE,
postgres(# "groupname" Varchar(20) NOT NULL,
postgres(# "description" Varchar(255),
postgres(# UNIQUE (id,groupname),
postgres(#  PRIMARY KEY ("id")
postgres(# );
NOTICE:  CREATE TABLE will create implicit sequence 'group_id_seq' for SERIAL column 'group.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'group_pkey' for table 'group'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'group_id_key' for table 'group'
CREATE TABLE

运行结果也没有错，现在插入数据。
insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
insert into "group"(groupname,description) values('Guest','xxxxxxxxxxxxxxxxxx');
insert into "group"(groupname,description) values('Domain','xxxxxxxxxxxxxxxxxx');
postgres=# insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
INSERT 110466 1
postgres=# insert into "group"(groupname,description) values('Guest','xxxxxxxxxxxxxxxxxx');
INSERT 110467 1
postgres=# insert into "group"(groupname,description) values('Domain','xxxxxxxxxxxxxxxxxx');
INSERT 110468 1
postgres=#
postgres=# insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
INSERT 110469 1
postgres=# insert into "group"(groupname,description) values('Guest','xxxxxxxxxxxxxxxxxx');
INSERT 110470 1
postgres=# insert into "group"(groupname,description) values('Domain','xxxxxxxxxxxxxxxxxx');
INSERT 110471 1
postgres=# insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
INSERT 110472 1
postgres=# insert into "group"(groupname,description) values('Guest','xxxxxxxxxxxxxxxxxx');
INSERT 110473 1
postgres=# insert into "group"(groupname,description) values('Domain','xxxxxxxxxxxxxxxxxx');
INSERT 110474 1
postgres=# insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
INSERT 110475 1
postgres=# insert into "group"(groupname,description) values('Guest','xxxxxxxxxxxxxxxxxx');
INSERT 110476 1
postgres=# insert into "group"(groupname,description) values('Domain','xxxxxxxxxxxxxxxxxx');
INSERT 110477 1
postgres=# insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
INSERT 110478 1
postgres=# insert into "group"(groupname,description) values('Guest','xxxxxxxxxxxxxxxxxx');
INSERT 110479 1
postgres=# insert into "group"(groupname,description) values('Domain','xxxxxxxxxxxxxxxxxx');
INSERT 110480 1
postgres=# select * from "group";
 id | groupname |    description
----+-----------+--------------------
  1 | Admin     | xxxxxxxxxxxxxxxxxx
  2 | Guest     | xxxxxxxxxxxxxxxxxx
  3 | Domain    | xxxxxxxxxxxxxxxxxx
  4 | Admin     | xxxxxxxxxxxxxxxxxx
  5 | Guest     | xxxxxxxxxxxxxxxxxx
  6 | Domain    | xxxxxxxxxxxxxxxxxx
  7 | Admin     | xxxxxxxxxxxxxxxxxx
  8 | Guest     | xxxxxxxxxxxxxxxxxx
  9 | Domain    | xxxxxxxxxxxxxxxxxx
 10 | Admin     | xxxxxxxxxxxxxxxxxx
 11 | Guest     | xxxxxxxxxxxxxxxxxx
 12 | Domain    | xxxxxxxxxxxxxxxxxx
 13 | Admin     | xxxxxxxxxxxxxxxxxx
 14 | Guest     | xxxxxxxxxxxxxxxxxx
 15 | Domain    | xxxxxxxxxxxxxxxxxx
(15 rows)

但你会发现对groupname字段的唯一约束不起使用。失效原因：
"id" Serial NOT NULL UNIQUE, (唯一约束)
UNIQUE (id,groupname), (id字段又做了一次唯一约束)
这就是它失效的原因。正确的脚本写法是：
Create table "group"
(
       "id" Serial NOT NULL UNIQUE,
       "groupname" Varchar(20) NOT NULL,
       "description" Varchar(255),
       UNIQUE (groupname),
      PRIMARY KEY ("id")
);
member=> insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
INSERT 110497 1
member=> insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
ERROR:  Cannot insert a duplicate key into unique index group_groupname_key
member=> insert into "group"(groupname,description) values('Admin','xxxxxxxxxxxxxxxxxx');
ERROR:  Cannot insert a duplicate key into unique index group_groupname_key

	提示
	Psql 命令行返回ERROR: Cannot insert a duplicate key into unique index group_groupname_key 唯一约束成功。