Bruce Momjian wrote:
> For primary key, there is no enforcement of the primary key, e.g.:
>
> test=> CREATE TABLE parent (name TEXT);
> CREATE TABLE
> test=> CREATE TABLE child (age INT) inherits (parent) ;
> CREATE TABLE
> test=> ALTER TABLE parent ADD primary KEY (name);
> NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "parent_pkey" for table "parent"
> ALTER TABLE
> test=> INSERT INTO parent (name) VALUES ('a');
> INSERT 0 1
> test=> INSERT INTO child (name) VALUES ('a');
> INSERT 0 1
> test=> SELECT * FROM parent;
> name
> ------
> a
> a
> (2 rows)
>
> So, it seems like this is the ugly truth of our inheritance limitations
> with primary key, and unless we can fix the primary key issues with
> inheritance, our current behavior is the more predictable we can hope for.
[ Thread moved to hackers because this might be a valid bug. ]
Summary: ALTER TABLE SET NOT NULL on a parent table is passed to the
child, while ALTER TABLE ADD PRIMARY KEY is not, particularly the NOT
NULL part of the PRIMARY KEY specification.
OK, now I understand what you are getting at --- the following returns a
NULL value from the parent:
test=> CREATE TABLE parent (name text);
CREATE TABLE
test=> CREATE TABLE child (age int) INHERITS (parent) ;
CREATE TABLE
test=> ALTER TABLE parent ADD PRIMARY KEY (name);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"parent_pkey" for table "parent"
ALTER TABLE
test=> INSERT INTO child (name) VALUES (null);
INSERT 0 1
test=> \pset null '(null)'
Null display is "(null)".
test=> SELECT * FROM parent;
name
--------
(null)
(1 row)
while the parent has a NOT NULL specification:
test=> \d parent
Table "public.parent"
Column | Type | Modifiers
--------+------+-----------
name | text | not null
Indexes:
"parent_pkey" PRIMARY KEY, btree (name)
Number of child tables: 1 (Use \d+ to list them.)
That does seem like something that should be fixed.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
