Обсуждение: 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_ptypeCHECK (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 | Identifiername
| text | not null | Nameaddress | integer | | Primary address
idsalutation| text | default 'Dear Sir' | Salutation in a letterenvelope | text |
| Form of name on envelopeemail | text | | Email addresswww | text |
| Web URLcontact | character(10) | | Id of primary contact personstructure |
character(1) | | Legal structure codedepartment | text | | Name of this
departmentparent_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 inheritedproperly from the parent. >In other words you couldn't say PRIMARY KEY for an inherited column >unless it is NOTNULL (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'thave 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