標籤:
資料庫表有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