7.1 (current) unwanted NOT NULL constraint inserted

From: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
To: pgsql-hackers(at)postgresql(dot)org
Subject: 7.1 (current) unwanted NOT NULL constraint inserted
Date: 2000-12-15 17:53:13
Message-ID: 200012151753.eBFHrEr10390@linda.lfix.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(dot)Elphick(at)lfix(dot)co(dot)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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2000-12-15 18:23:05 Re: RPM changes for 7.1.
Previous Message Hannu Krosing 2000-12-15 17:34:06 Re: (Updated) Table File Format