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 DATABASE postgres=# postgres=# postgres=# postgres=# \c test You are now connected to database "test" as user "postgres". test=# create table tbl_null (a int not null,b varchar(12)); CREATE TABLE test=# insert into tbl_null (a,b) values(1,'1'); INSERT 0 1 test=# insert into tbl_null (a) values(2); INSERT 0 1 test=# insert into tbl_null (b) values('3'); ERROR: null value in column "a" violates not-null constraint DETAIL: 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 values test=# delete from tbl_null where b is null; DELETE 1 test=# alter table tbl_null alter COLUMN b set not null; ALTER TABLE test=# \d tbl_null Table "public.tbl_null" Column | Type | Modifiers --------+-----------------------+----------- a | integer | not null b | character varying(12) | not null test=# 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 TABLE test=# \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 TABLE test=# \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 test=# drop table tbl_default ; DROP TABLE test=# create table tbl_default(a int not null,b varchar(12) not null); CREATE TABLE test=# alter table tbl_default alter COLUMN b set default 'try me'; ALTER TABLE test=# \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 1 test=# insert into tbl_default (a) values(2); INSERT 0 1 test=# 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 TABLE test=# \d tbl_default Table "public.tbl_default" Column | Type | Modifiers --------+-----------------------+---------------------------------------------- a | integer | not null b | character varying(12) | not null default 'my god'::character varying test=# alter table tbl_default alter COLUMN b drop default; ALTER TABLE test=# \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 TABLE test=# drop table tbl_check ; DROP TABLE test=# create table tbl_check test-# ( 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 TABLE test=# create table tbl_check ( a int not null, b varchar(12) not null); CREATE TABLE test=# alter table tbl_check add constraint ck_tbl_check_a check (a > 0); ALTER TABLE test=# alter table tbl_check add constraint ck_tbl_check_b check (b in ('ab','aB','Ab','AB')); ALTER TABLE test=# \d tbl_check Table "public.tbl_check" Column | Type | Modifiers --------+-----------------------+----------- a | integer | not null b | character varying(12) | not null Check 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 1 test=# 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 TABLE test=# 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 row test=# delete from tbl_check where a <= 0; DELETE 1 test=# alter table tbl_check add constraint ck_tbl_check_a check (a > 0); ALTER TABLE */
