7.1 (current) unwanted NOT NULL constraint inserted
In the creation below, the column parent_id has ended up with a NOT NULL
constraint that I didn't ask for and don't want.
This is 7.1, updated today from cvs.
=======================================================================
[... other tables created ...]
CREATE TABLE person
(
ptype SMALLINT,
id CHAR(10),
name TEXT NOT NULL,
address INTEGER,
salutation TEXT DEFAULT 'Dear Sir',
envelope TEXT,
email TEXT,
www TEXT,
CONSTRAINT person_ptype CHECK (ptype >= 0 AND ptype <= 8)
,
PRIMARY KEY (id),
FOREIGN KEY (id, address) REFERENCES address(person, id)
ON UPDATE CASCADE
ON DELETE RESTRICT
)
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'person_pkey' for
table 'person'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
[... other tables created ...]
CREATE TABLE organisation
(
contact CHAR(10) REFERENCES individual (id)
ON UPDATE CASCADE
ON DELETE NO ACTION,
structure CHAR(1) CHECK (structure='L' OR
structure='C' OR
structure='U' OR
structure='O'),
department TEXT,
parent_id CHAR(10),
CONSTRAINT dept_parent CHECK ((department IS NULL AND parent_id IS NULL) OR
(department > '' AND parent_id > '')),
CONSTRAINT organisation_ptype CHECK ((ptype >= 2 AND ptype <=4) OR ptype =
8)
,
PRIMARY KEY (id),
FOREIGN KEY (id, address) REFERENCES address(person, id)
ON UPDATE CASCADE
ON DELETE RESTRICT
)
INHERITS (person)
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'organisation_pkey' for table 'organisation'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
[... skip various COMMENT declarations ...]
\d+ organisation
Table "organisation"
Attribute | Type | Modifier | Description
------------+---------------+--------------------+-----------------------------
-
ptype | smallint | |
id | character(10) | not null | Identifier
name | text | not null | Name
address | integer | | Primary address id
salutation | text | default 'Dear Sir' | Salutation in a letter
envelope | text | | Form of name on envelope
email | text | | Email address
www | text | | Web URL
contact | character(10) | | Id of primary contact person
structure | character(1) | | Legal structure code
department | text | | Name of this department
parent_id | character(10) | not null | Parent organisation id
Index: organisation_pkey
Constraints: ((ptype >= 0) AND (ptype <= 8))
((((structure = 'L'::bpchar) OR (structure = 'C'::bpchar)) OR
(structure = 'U'::bpchar)) OR (structure = 'O'::bpchar))
(((department ISNULL) AND (parent_id ISNULL)) OR (((department >
''::text) AND (parent_id NOTNULL)) AND (parent_id > ''::bpchar)))
(((ptype >= 2) AND (ptype <= 4)) OR (ptype = 8))=======================================================================
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"The fear of the LORD is the instruction of wisdom, and
before honour is humility." Proverbs 15:33
I can't reproduce this --- I get
Constraints: ((ptype >= 0) AND (ptype <= 8))
((((structure = 'L'::bpchar) OR (structure = 'C'::bpchar)) OR (structure = 'U'::bpchar)) OR (structure = 'O'::bpchar))
(((department ISNULL) AND (parent_id ISNULL)) OR ((department > ''::text) AND (parent_id > ''::bpchar)))
(((ptype >= 2) AND (ptype <= 4)) OR (ptype = 8))
However, I had to guess about the referenced tables, and possibly I
guessed wrong. Could you supply their declarations too?
regards, tom lane
OK, I see the problem. You have:
CREATE TABLE person (
id CHAR(10)
);
CREATE TABLE organisation (
...,
PRIMARY KEY (id)
)
INHERITS (person);
ie, a PRIMARY KEY declaration on an inherited column. Normally a
PRIMARY KEY declaration causes the key column to become marked
NOT NULL --- but if the key column is an inherited one then the
code misapplies the mark to the last non-inherited column ... or
coredumps if there are no non-inherited columns :-(. See line 995
in parse/analyze.c.
While it's easy enough to avoid the mis-marking of the last column,
causing the right thing to happen instead is much less easy. What
we really want is for the key column to be marked NOT NULL,
but during analyze.c there isn't a set of ColumnDefs for the inherited
columns, and so there's no place to put the mark.
Short of a major restructuring of inherited-column creation, I see
no good solution to this. I see two bad solutions:
1. Require that the referenced column be marked NOT NULL already,
so that the constraint will be inherited properly from the parent.
In other words you couldn't say PRIMARY KEY for an inherited column
unless it is NOT NULL (or a fortiori, PRIMARY KEY) in the parent table.
2. Do nothing, in effect silently dropping the NOT NULL constraint
for such a column. (Actually we don't have to be silent about it;
we could emit a NOTICE when the parent doesn't have NOT NULL.)
IMHO, #1 is a little less bad, but I'm not firmly committed to it.
Comments anyone?
regards, tom lane
Tom Lane wrote:
...
Short of a major restructuring of inherited-column creation, I see
no good solution to this. I see two bad solutions:1. Require that the referenced column be marked NOT NULL already,
so that the constraint will be inherited properly from the parent.
In other words you couldn't say PRIMARY KEY for an inherited column
unless it is NOT NULL (or a fortiori, PRIMARY KEY) in the parent table.2. Do nothing, in effect silently dropping the NOT NULL constraint
for such a column. (Actually we don't have to be silent about it;
we could emit a NOTICE when the parent doesn't have NOT NULL.)IMHO, #1 is a little less bad, but I'm not firmly committed to it.
Comments anyone?
In the absence of properly working inheritance, I would vote for 1. (I
am only declaring PRIMARY KEY on the inherited column because that
constraint doesn't get inherited as (I think) it should.) Option 2 would
give a wrongly-defined table.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"The fear of the LORD is the instruction of wisdom, and
before honour is humility." Proverbs 15:33
Import Notes
Reply to msg id not found: MessagefromTomLanetgl@sss.pgh.pa.usofFri15Dec2000165653EST.13810.976917413@sss.pgh.pa.us | Resolved by subject fallback