postgresql----資料庫表的約束----NOT NULL,DEFAULT,CHECK

標籤：

資料庫表有NOT NULL,DEFAULT,CHECK,UNIQUE,PRIMARY KEY,FOREIGN KEY六種約束。

一、NOT NULL ---- 非空約束

NULL表示沒有資料，不表示具體的數值，所以在資料庫中NULL是不等於NULL的。判斷表中的一個儲存格是不是NULL使用的是IS NULL或者IS NOT NULL，而不是=NULL或者!=NULL，當一個欄位設定NOT NULL約束後，INSERT時必須給該欄位賦值，否則拒絕寫入。在一些程式語言（如C）查詢結果中出現NULL有可能會直接作為空白指標，如果使用不當，會直接導致程式崩潰。所以一個欄位要儘可能的設定NOT NULL約束，或者DEFAULT約束，當然OUTER JOIN的結果也有可能引入NULL，所以開發過程中要儘可能的做好保護。

1.設定NOT NULL約束的欄位INSERT必須賦值，沒有NOT NULL約束的欄位INSERT沒有賦值，會自動填滿NULL。

/*postgres=# create database test with template = template0 encoding=‘UTF8‘ lc_collate=‘C‘ lc_ctype=‘C‘;CREATE DATABASEpostgres=# postgres=# postgres=# postgres=# \c testYou are now connected to database "test" as user "postgres".test=# create table tbl_null (a int not null,b varchar(12));CREATE TABLEtest=# insert into tbl_null (a,b) values(1,‘1‘);INSERT 0 1test=# insert into tbl_null (a) values(2);INSERT 0 1test=# insert into tbl_null (b) values(‘3‘);ERROR:  null value in column "a" violates not-null constraintDETAIL:  Failing row contains (null, 3).test=# select * from tbl_null; a | b ---+--- 1 | 1 2 | (2 rows)*/

 2.NOT NULL約束增加

已存在的欄位設定NOT NULL約束前必須先刪除為NULL的資料行。

/*test=# alter table tbl_null alter COLUMN b set not null;ERROR:  column "b" contains null valuestest=# delete from tbl_null where b is null;DELETE 1test=# alter table tbl_null alter COLUMN b set not null;ALTER TABLEtest=# \d tbl_null           Table "public.tbl_null" Column |         Type          | Modifiers --------+-----------------------+----------- a      | integer               | not null b      | character varying(12) | not nulltest=# select * from tbl_null ; a | b ---+--- 1 | 1(1 row)*/

 3.刪除NOT NULL約束

/*test=# alter table tbl_null alter COLUMN b drop not null;ALTER TABLEtest=# \d tbl_null           Table "public.tbl_null" Column |         Type          | Modifiers --------+-----------------------+----------- a      | integer               | not null b      | character varying(12) | */

 二、DEFAULT ---- 預設值

 INSERT沒有賦值的欄位預設填充NULL（前提是該欄位沒有NOT NULL約束），設定DEFAULT預設值，INSERT沒有賦值會預設填充該預設值。尤其是設定NOT NULL約束的欄位，如果給定一個DEFAULT約束，即使INSERT沒有給欄位賦值也不會出錯。

1.設定DEFAULT約束,既可以在建立表時直接設定，也可以在建立表後修改欄位，欄位新增預設值約束可以不用考慮已有資料。

/*test=# create table tbl_default(a int not null,b varchar(12) not null default ‘try me‘);CREATE TABLEtest=# \d tbl_default                           Table "public.tbl_default" Column |         Type          |                  Modifiers                   --------+-----------------------+---------------------------------------------- a      | integer               | not null b      | character varying(12) | not null default ‘try me‘::character varyingtest=# drop table tbl_default ;DROP TABLEtest=# create table tbl_default(a int not null,b varchar(12) not null);CREATE TABLEtest=# alter table tbl_default alter COLUMN b set default ‘try me‘;ALTER TABLEtest=# \d tbl_default                           Table "public.tbl_default" Column |         Type          |                  Modifiers                   --------+-----------------------+---------------------------------------------- a      | integer               | not null b      | character varying(12) | not null default ‘try me‘::character varying*/

2.INSERT時賦值使用賦值填充，否則使用預設值填充。

/*test=# insert into tbl_default (a,b) values(1,‘aloha‘);INSERT 0 1test=# insert into tbl_default (a) values(2);INSERT 0 1test=# select * from tbl_default ; a |   b    ---+-------- 1 | aloha 2 | try me(2 rows)*/

3.預設值約束的修改與刪除，修改預設值直接新設定一個預設值即可。

/*test=# alter table tbl_default alter COLUMN b set default ‘my god‘;ALTER TABLEtest=# \d tbl_default                           Table "public.tbl_default" Column |         Type          |                  Modifiers                   --------+-----------------------+---------------------------------------------- a      | integer               | not null b      | character varying(12) | not null default ‘my god‘::character varyingtest=# alter table tbl_default alter COLUMN b drop default;ALTER TABLEtest=# \d tbl_default          Table "public.tbl_default" Column |         Type          | Modifiers --------+-----------------------+----------- a      | integer               | not null b      | character varying(12) | not null*/

 

三、CHECK ---- 檢查約束

 INSERT,UPDATE時檢查欄位值是否滿足CHECK條件，若不滿足則拒絕寫入。

1.CHECK約束的設定

/*test=# create table tbl_check(a int not null check (a>0),b varchar(12) not null check (b in (‘ab‘,‘Ab‘,‘aB‘,‘AB‘)));CREATE TABLEtest=# drop table tbl_check ;DROP TABLEtest=# create table tbl_checktest-# (test(# a int not null,test(# b varchar(12) not null,test(# constraint ck_tbl_check_a check (a > 0),test(# constraint ck_tbl_check_b check (b in (‘ab‘,‘aB‘,‘Ab‘,‘AB‘))test(# );CREATE TABLEtest=# create table tbl_check(a int not null,b varchar(12) not null);CREATE TABLEtest=# alter table tbl_check add constraint ck_tbl_check_a check (a > 0);ALTER TABLEtest=# alter table tbl_check add constraint ck_tbl_check_b check (b in (‘ab‘,‘aB‘,‘Ab‘,‘AB‘));ALTER TABLEtest=# \d tbl_check           Table "public.tbl_check" Column |         Type          | Modifiers --------+-----------------------+----------- a      | integer               | not null b      | character varying(12) | not nullCheck constraints:    "ck_tbl_check_a" CHECK (a > 0)    "ck_tbl_check_b" CHECK (b::text = ANY (ARRAY[‘ab‘::character varying, ‘aB‘::character varying, ‘Ab‘::character varying, ‘AB‘::character varying]::text[]))*/

2.以上表tbl_check為例，INSERT時a的值必須是大於0的整數，b的值只能在‘ab‘,‘aB‘,‘Ab‘,‘AB‘範圍內。

/*test=# insert into tbl_check (a,b) values(1,‘ab‘);INSERT 0 1test=# insert into tbl_check (a,b) values(-1,‘ab‘);ERROR:  new row for relation "tbl_check" violates check constraint "ck_tbl_check_a"DETAIL:  Failing row contains (-1, ab).test=# insert into tbl_check (a,b) values(1,‘ac‘);ERROR:  new row for relation "tbl_check" violates check constraint "ck_tbl_check_b"DETAIL:  Failing row contains (1, ac).*/

3.CHECK約束的刪除

/*test=# alter table tbl_check drop constraint ck_tbl_check_a;ALTER TABLEtest=# insert into tbl_check (a,b) values(-1,‘ab‘);INSERT 0 1*/

4.CHECK約束的增加

新增CHECK約束必須首先刪除已存在的不滿足約束的資料

/*test=# alter table tbl_check add constraint ck_tbl_check_a check (a > 0);ERROR:  check constraint "ck_tbl_check_a" is violated by some rowtest=# delete from tbl_check where a <= 0;DELETE 1test=# alter table tbl_check add constraint ck_tbl_check_a check (a > 0);ALTER TABLE*/

 

postgresql----資料庫表的約束----NOT NULL,DEFAULT,CHECK