postgresql----繼承表inherits parent table

標籤：

 

使用INHERITS建立的新表會繼承一個或多個父表，子表只會繼承父表的表結構和NOT NULL，DEFAULT，CHECK三種約束，主鍵，外鍵和唯一鍵以及索引不會被繼承，所以修改父表的結構（增刪欄位），NOT NULL，DEFAULT和CHECK約束會自動同步子表修改。

樣本1.

create table tbl_inherits_parent(a int not null,b varchar(32) not null default ‘Got u‘,c int check (c > 0),d date not null);test=# alter table tbl_inherits_parent add constraint pk_tbl_inherits_parent_a primary key(a);ALTER TABLEtest=# alter table tbl_inherits_parent add constraint uk_tbl_inherits_parent_b_d unique (b,d);ALTER TABLEtest=# create table tbl_inherits_partition() inherits (tbl_inherits_parent);CREATE TABLEtest=# \d tbl_inherits_partition                     Table "public.tbl_inherits_partition" Column |         Type          |                  Modifiers                  --------+-----------------------+--------------------------------------------- a      | integer               | not null b      | character varying(32) | not null default ‘Got u‘::character varying c      | integer               |  d      | date                  | not nullCheck constraints:    "tbl_inherits_parent_c_check" CHECK (c > 0)Inherits: tbl_inherits_parent

 

樣本2.

test=# alter table tbl_inherits_parent add column e int not null default 0;ALTER TABLEtest=# alter table tbl_inherits_parent alter column b set default ‘try me‘;ALTER TABLEtest=# \d tbl_inherits_partition                      Table "public.tbl_inherits_partition" Column |         Type          |                  Modifiers                   --------+-----------------------+---------------------------------------------- a      | integer               | not null b      | character varying(32) | not null default ‘try me‘::character varying c      | integer               |  d      | date                  | not null e      | integer               | not null default 0Check constraints:    "tbl_inherits_parent_c_check" CHECK (c > 0)Inherits: tbl_inherits_parent

 

樣本3.

除繼承父表之外，建立子表時可以增加自己的欄位

test=# create table tbl_inherits_partition1(f int) inherits (tbl_inherits_parent);CREATE TABLEtest=# \d tbl_inherits_partition1                     Table "public.tbl_inherits_partition1" Column |         Type          |                  Modifiers                   --------+-----------------------+---------------------------------------------- a      | integer               | not null b      | character varying(32) | not null default ‘try me‘::character varying c      | integer               |  d      | date                  | not null e      | integer               | not null default 0 f      | integer               | Check constraints:    "tbl_inherits_parent_c_check" CHECK (c > 0)Inherits: tbl_inherits_parent

 

樣本4.解除繼承

test=# alter table tbl_inherits_partition1 no inherit tbl_inherits_parent;ALTER TABLEtest=# \d tbl_inherits_partition1                     Table "public.tbl_inherits_partition1" Column |         Type          |                  Modifiers                   --------+-----------------------+---------------------------------------------- a      | integer               | not null b      | character varying(32) | not null default ‘try me‘::character varying c      | integer               |  d      | date                  | not null e      | integer               | not null default 0 f      | integer               | Check constraints:    "tbl_inherits_parent_c_check" CHECK (c > 0)

 

postgresql----繼承表inherits parent table