Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | Github | OSChina 博客 | 雲社區 | 雲棲社區 | Facebook | Linkedin | 知乎專欄 | 視頻教程 | About
5.5. 約束
上一頁 第 5 章 數據定義（DDL） 下一頁

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 唯一約束成功。


上一頁 上一級 下一頁
5.4. 序列 起始頁 5.6. 主鍵/外鍵