OS:CentOS 6.2
DB:Postgres 9.1.3
1.建表语句
| CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name OF type_name [ ( { column_name WITH OPTIONS [ column_constraint [ ... ] ] | table_constraint } [,... ] ) ] [ WITH ( storage_parameter [= value] [,... ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] where column_constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) [ NO INHERIT ] | DEFAULT default_expr | UNIQUE index_parameters | PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] and table_constraint is: [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | UNIQUE ( column_name [,... ] ) index_parameters | PRIMARY KEY ( column_name [,... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [,... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [,... ] ) REFERENCES reftable [ ( refcolumn [,... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] |
2.文档释义
| DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently,only UNIQUE,PRIMARY KEY,EXCLUDE,and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. INITIALLY IMMEDIATE INITIALLY DEFERRED If a constraint is deferrable,this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE,it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED,it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command. SET CONSTRAINTS { ALL | name [,...] } { DEFERRED | IMMEDIATE } |
大概说明一下， DEFERRABLE| NOT DEFERRABLE这个参数控制着约束是否可被延迟生效，当约束设置是非延迟时将会对每一个命令立即进行检查，当约束设置可延迟生效时可以使用set constraints命令来推迟检查直到事务结束，默认是非延迟。当前，只有UNIQUE，PK，exclude，FK可以延迟，NOT NULL和CHECK都是非延迟，这点和ORACLE有所不同，oracle中check也可以延迟。
当约束是可延迟生效时，这个可以选择检查时间来检查约束，如INITIALLY IMMEDIATE，它会在每一个语句执行后进行检查，而INITIALLY DEFERRED则会只在事务结束时才检查，选项可以通过SET CONSTRAINTS来更改。
set constraints中可以设置all或者name,name是约束名称，可以在pg_constraint中查询，可见下例
总的来说
非延迟是立即生效的，不允许延迟
延迟生效初始化立即生效是对每个语句结束时检查
延迟生效初始化延迟生效是直到事务完成才检查(可使用set constraints调整)
3.例子说明
a.非延迟(not deferrable)
| [postgres@localhost ~]$ psql -d db_kenyon Password: psql (9.1.3) Type "help" for help. db_kenyon=# create table t_kenyon(id int primary key not deferrable); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_kenyon_pkey" for table "t_kenyon" CREATE TABLE db_kenyon=# insert into t_kenyon values(1); INSERT 0 1 db_kenyon=# begin; BEGIN db_kenyon=# insert into t_kenyon values(1); ERROR: duplicate key value violates unique constraint "t_kenyon_pkey" DETAIL: Key (id)=(1) already exists. db_kenyon=# begin; BEGIN db_kenyon=# set constraints all deferred; SET CONSTRAINTS db_kenyon=# insert into t_kenyon values(1); ERROR: duplicate key value violates unique constraint "t_kenyon_pkey" DETAIL: Key (id)=(1) already exists. |
b.延迟生效之 INITIALLY IMMEDIATE
| db_kenyon=# drop table t_kenyon; DROP TABLE db_kenyon=# create table t_kenyon(id int primary key deferrable initially immediate); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_kenyon_pkey" for table "t_kenyon" CREATE TABLE db_kenyon=# insert into t_kenyon values(1); INSERT 0 1 db_kenyon=# insert into t_kenyon values(1); ERROR: duplicate key value violates unique constraint "t_kenyon_pkey" DETAIL: Key (id)=(1) already exists. db_kenyon=# begin transaction; BEGIN db_kenyon=# insert into t_kenyon values(1); ERROR: duplicate key value violates unique constraint "t_kenyon_pkey" DETAIL: Key (id)=(1) already exists. db_kenyon=# rollback; ROLLBACK db_kenyon=# end; WARNING: there is no transaction in progress COMMIT db_kenyon=# begin transaction; BEGIN db_kenyon=# set constraints all deferred; SET CONSTRAINTS db_kenyon=# insert into t_kenyon values(1); INSERT 0 1 db_kenyon=# set constraints all immediate; ERROR: duplicate key value violates unique constraint "t_kenyon_pkey" DETAIL: Key (id)=(1) already exists. |
c.延迟生效之 INITIALLY DEFERRED
| db_kenyon=# create table t_kenyon(id int primary key deferrable initially deferred); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_kenyon_pkey" for table "t_kenyon" CREATE TABLE db_kenyon=# insert into t_kenyon values(1); INSERT 0 1 db_kenyon=# begin; BEGIN db_kenyon=# insert into t_kenyon values(1); INSERT 0 1 db_kenyon=# insert into t_kenyon values(2); INSERT 0 1 db_kenyon=# commit; ERROR: duplicate key value violates unique constraint "t_kenyon_pkey" DETAIL: Key (id)=(1) already exists. db_kenyon=# end; WARNING: there is no transaction in progress COMMIT db_kenyon=# select conname from pg_constraint where contype = 'p'; conname --------------- t_kenyon_pkey (1 row) db_kenyon=# begin; BEGIN db_kenyon=# insert into t_kenyon values(1); INSERT 0 1 db_kenyon=# set constraints t_kenyon_pkey immediate; ERROR: duplicate key value violates unique constraint "t_kenyon_pkey" DETAIL: Key (id)=(1) already exists. |
