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

來源:互聯網
上載者:User

標籤:

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

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.