The following bug has been logged online:
Bug reference: 5064
Logged by: Tomonari Katsumata
Email address: katsumata.tomonari@oss.ntt.co.jp
PostgreSQL version: 8.5dev, 8.4.1
Operating system: Windows XP, RHEL 5.2
Description: not-null constraints is not inherited
Details:
Hi,
I found a bug about inheritance.
The user manual says, "All check constraints and not-null constraints on a
parent table are automatically inherited by its children."
But, the behavier is not true. The sample is like below.
postgres=# CREATE TABLE parent (id integer);
postgres=# CREATE TABLE child () inherits (parent);
postgres=# ALTER TABLE parent ADD PRIMARY KEY (id);
postgres=# \d+ parent
Table "public.parent"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
id | integer | not null | plain |
Indexes:
"parent_pkey" PRIMARY KEY, btree (id)
Child tables: child
Has OIDs: no
postgres=# \d+ child
Table "public.child"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
id | integer | ã | plain |
Inherits: parent
Has OIDs: no
child table does not have a "not null" constraints.
I think it's not desirable behavier.
On the other hand, if I set a constraints using
"ALTER COLUMN SET NOT NULL", the constraints is inherited by its child.
postgres=# DROP TABLE parent, child;
postgres=# CREATE TABLE parent (id integer);
postgres=# CREATE TABLE child () inherits (parent);
postgres=# ALTER TABLE parent ALTER COLUMN id SET NOT NULL;
postgres=# \d+ parent
Table "public.parent"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
id | integer | not null | plain |
Child tables: child
Has OIDs: no
postgres=# \d+ child
Table "public.child"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
id | integer | not null | plain |
Inherits: parent
Has OIDs: no
regards,
--
Tomonari Katsumata
katsumata.tomonari@oss.ntt.co.jp