-- test deinherit procedure

--

-- deinherit inh_child1

create tableinh_grandchld () inherits (inh_child1);

create tableinh_child3 () inherits (inh_child1);

alter table inh_child1 no inherit inh_parent;

\d+ inh_parent

Table "public.inh_parent"

Not-null constraints:

"inh_child1_f1_not_null" NOT NULL "f1"

Child tables: inh_child2,

\d+ inh_child2

Table "public.inh_child2"

select conrelid::regclass, conname, contype, coninhcount, conislocal

from pg_constraint where contype = 'n' and

conrelid::regclass::text in ('inh_parent', 'inh_child1', 'inh_child2', 'inh_grandchld')

conrelid::regclass::text in ('inh_parent', 'inh_child1', 'inh_child2', 'inh_child3')

order by 2, 1;

conrelid | conname | contype | coninhcount | conislocal

---------------+------------------------+---------+-------------+------------

inh_child1| inh_child1_f1_not_null | n | 0 | t

inh_grandchld | inh_child1_f1_not_null | n | 1 | f

inh_child2| inh_child2_f1_not_null | n | 1 | t

inh_parent| inh_parent_f1_not_null | n | 0 | t

conrelid | conname | contype | coninhcount | conislocal

------------+------------------------+---------+-------------+------------

inh_child1 | inh_child1_f1_not_null | n | 0 | t

inh_child3 | inh_child1_f1_not_null | n | 1 | f

inh_child2 | inh_child2_f1_not_null | n | 1 | t

inh_parent | inh_parent_f1_not_null | n | 0 | t

(4 rows)

drop table inh_parent, inh_child1, inh_child2,inh_grandchld;

drop table inh_parent, inh_child1, inh_child2,inh_child3;

-- a PK in parent must have a not-null in child that it can mark inherited

create table inh_parent (a int primary key);

create table inh_child (a int primary key);

create table inh_parent(f1 int not null);

create table inh_child1() inherits(inh_parent);

create table inh_child2() inherits(inh_parent);

create tableinh_grandchld() inherits(inh_child1, inh_child2);

create tableinh_child3() inherits(inh_child1, inh_child2);

NOTICE: merging multiple inherited definitions of column "f1"

-- show constraint info

select conrelid::regclass, conname, contype, coninhcount, conislocal

from pg_constraint where contype = 'n' and

conrelid in ('inh_parent'::regclass, 'inh_child1'::regclass, 'inh_child2'::regclass, 'inh_grandchld'::regclass)

conrelid in ('inh_parent'::regclass, 'inh_child1'::regclass, 'inh_child2'::regclass, 'inh_child3'::regclass)

order by 2, conrelid::regclass::text;

conrelid | conname | contype | coninhcount | conislocal

---------------+------------------------+---------+-------------+------------

inh_child1| inh_parent_f1_not_null | n | 1 | f

inh_child2| inh_parent_f1_not_null | n | 1 | f

inh_grandchld | inh_parent_f1_not_null | n | 2 | f

inh_parent| inh_parent_f1_not_null | n | 0 | t

conrelid | conname | contype | coninhcount | conislocal

------------+------------------------+---------+-------------+------------

inh_child1 | inh_parent_f1_not_null | n | 1 | f

inh_child2 | inh_parent_f1_not_null | n | 1 | f

inh_child3 | inh_parent_f1_not_null | n | 2 | f

inh_parent | inh_parent_f1_not_null | n | 0 | t

(4 rows)

drop table inh_parent cascade;

NOTICE: drop cascades to 3 other objects

DETAIL: drop cascades to table inh_child1

drop cascades to table inh_child2

drop cascades to tableinh_grandchld

drop cascades to tableinh_child3

-- test child table with inherited columns and

-- with explicitly specified not null constraints

create table inh_parent_1(f1 int);

createtableinh_grandchld () inherits (inh_child1);

createtableinh_child3 () inherits (inh_child1);

altertable inh_child1 no inherit inh_parent;

\d+ inh_parent

\d+ inh_child1

\d+ inh_child2

select conrelid::regclass, conname, contype, coninhcount, conislocal

from pg_constraintwhere contype='n'and

conrelid::regclass::textin ('inh_parent','inh_child1','inh_child2','inh_grandchld')

conrelid::regclass::textin ('inh_parent','inh_child1','inh_child2','inh_child3')

order by2,1;

droptable inh_parent, inh_child1, inh_child2,inh_grandchld;

droptable inh_parent, inh_child1, inh_child2,inh_child3;

createtableinh_parent (aintprimary key);

createtableinh_parent(f1intnot null);

createtableinh_child1() inherits(inh_parent);

createtableinh_child2() inherits(inh_parent);

createtableinh_grandchld() inherits(inh_child1, inh_child2);

createtableinh_child3() inherits(inh_child1, inh_child2);

select conrelid::regclass, conname, contype, coninhcount, conislocal

from pg_constraintwhere contype='n'and

conrelidin ('inh_parent'::regclass,'inh_child1'::regclass,'inh_child2'::regclass,'inh_grandchld'::regclass)

conrelidin ('inh_parent'::regclass,'inh_child1'::regclass,'inh_child2'::regclass,'inh_child3'::regclass)

order by2, conrelid::regclass::text;

droptable inh_parent cascade;