Skip site navigation (1) Skip section navigation (2)

BUG #5064: not-null constraints is not inherited

From: "Tomonari Katsumata" <katsumata(dot)tomonari(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5064: not-null constraints is not inherited
Date: 2009-09-18 10:05:27
Message-ID: 200909181005.n8IA5Ris061239@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5064
Logged by:          Tomonari Katsumata
Email address:      katsumata(dot)tomonari(at)oss(dot)ntt(dot)co(dot)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(dot)tomonari(at)oss(dot)ntt(dot)co(dot)jp

Responses

pgsql-bugs by date

Next:From: Robert HaasDate: 2009-09-18 13:37:21
Subject: Re: BUG #5063: MS Access crashes by quiting after linking tables with PostgreSQL
Previous:From: AnnitaDate: 2009-09-18 09:45:52
Subject: BUG #5063: MS Access crashes by quiting after linking tables with PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group