Nullable 'Foreign Key-like' Constraint

Ron,
I have done this by adding the attribute to the table with nulls allowed and adding a constraint to the table for the foreign key... works like a charm:

CREATE TABLE TESTTYPE (
TESTTYPEKEY char(30) NOT NULL,
TESTTYPENAME varchar(255) NULL,
TESTTYPEDESC varchar(255) NULL,
TESTTYPELABEL varchar(255) NULL,
CONSTRAINT XPKTESTTYPE
PRIMARY KEY (TESTTYPEKEY)
)
;

CREATE TABLE TEST (
TESTKEY char(30) NOT NULL,
TESTTYPEKEY char(30) NULL,
CONSTRAINT LOG_PK
PRIMARY KEY (TEST_PK),
CONSTRAINT testtype_test
FOREIGN KEY (TESTTYPEKEY)
REFERENCES TESTTYPE
)
;

Karen L. Grose
Vigilos Inc.

-----Original Message-----
From: Ron [mailto:rstpATlin@uxwav.esDOTcom]
Sent: Thursday, October 23, 2003 9:02 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Nullable 'Foreign Key-like' Constraint

I posted this to 'questions' yesterday instead of 'general' by mistake.
Sorry if anyone received duplicates.
----------------------------------------------------

Mandatories: Ver 7.3.4, Redhat Linux 8.0, P4, 2GB RAM

I want to add a 'nullable' foreign key to a column in a table. I have
tables "company" and "project" which may be related by
company.companyID <-> project.companyID.
project.companyID is allowed to be null. However, when someone tries to
delete a company which is still referenced in "project" I want a
constraint restricting deletion.

I tried:
ALTER TABLE company ADD CONSTRAINT company_is_ta
CHECK (companyID IN
(SELECT companyID FROM project));
and I receive:
ERROR: cannot use subselect in CHECK constraint expression

Then I came across this previous post which showed how to set it up when
the table is created. I tried it and it works for a new table, but I
can't get it to work with existing tables.

1) My attempt:
ALTER TABLE project ALTER COLUMN companyID SET DEFAULT NULL;
ALTER TABLE project ADD CONSTRAINT company_is_ta companyID
REFERENCES company(companyID);
(plus variations on the above, resulting in errors, all similar to:)
ERROR: parser: parse error at or near "companyID" at character 53

2) based on this previous posting:

From: Manfred Koizar (mkoi-pg@aon.at)
Subject: Re: NULL Foreign Key
Newsgroups:comp.databases.postgresql.general,
comp.databases.postgresql.questions
Date: 2002-07-17 05:51:19 PST

On Tue, 16 Jul 2002 17:10:32 -0700, "Kuhn, Dylan K (NDTI)"
<KuhnDK@navair.navy.mil> wrote:

Can I make a foreign key that is allowed to be NULL?

Yes:

fred=# CREATE TABLE father (i INT PRIMARY KEY);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'father_pkey' for table 'father'
CREATE
fred=# CREATE TABLE son (i INT REFERENCES father);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
fred=# INSERT INTO father VALUES (1);
INSERT 183317 1
fred=# INSERT INTO son VALUES (1);
INSERT 183318 1
fred=# INSERT INTO son VALUES (2);
ERROR: <unnamed> referential integrity violation - key referenced
from son not found in father
fred=# INSERT INTO son VALUES (NULL);
INSERT 183320 1

Servus
Manfred

Anyone know how I can get this to work? BTW I don't want to use 'ignore'
rules when someone attempts to delete the company as I want the
constraint message to be shown in the app's browser.

TIA
Ron

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Ron,
I have done this by adding the attribute to the table with nulls allowed and adding a constraint to the table for the foreign key... works like a charm:

CREATE TABLE TESTTYPE (
TESTTYPEKEY char(30) NOT NULL,
TESTTYPENAME varchar(255) NULL,
TESTTYPEDESC varchar(255) NULL,
TESTTYPELABEL varchar(255) NULL,
CONSTRAINT XPKTESTTYPE
PRIMARY KEY (TESTTYPEKEY)
)
;

CREATE TABLE TEST (
TESTKEY char(30) NOT NULL,
TESTTYPEKEY char(30) NULL,
CONSTRAINT LOG_PK
PRIMARY KEY (TEST_PK),
CONSTRAINT testtype_test
FOREIGN KEY (TESTTYPEKEY)
REFERENCES TESTTYPE
)
;
Karen L. Grose
Vigilos Inc.

Karen L. Grose
Vigilos Inc.
2030 First Avenue
Suite 300
Seattle, WA 98121
206.728.6464 ext. 111 :Phone
206.728.6440 :Fax
206.335-8386 :Cell

-----Original Message-----
From: Ron [mailto:rstpATlin@uxwav.esDOTcom]
Sent: Thursday, October 23, 2003 9:02 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Nullable 'Foreign Key-like' Constraint

I posted this to 'questions' yesterday instead of 'general' by mistake.
Sorry if anyone received duplicates.
----------------------------------------------------

Mandatories: Ver 7.3.4, Redhat Linux 8.0, P4, 2GB RAM

I want to add a 'nullable' foreign key to a column in a table. I have
tables "company" and "project" which may be related by
company.companyID <-> project.companyID.
project.companyID is allowed to be null. However, when someone tries to
delete a company which is still referenced in "project" I want a
constraint restricting deletion.

I tried:
ALTER TABLE company ADD CONSTRAINT company_is_ta
CHECK (companyID IN
(SELECT companyID FROM project));
and I receive:
ERROR: cannot use subselect in CHECK constraint expression

Then I came across this previous post which showed how to set it up when
the table is created. I tried it and it works for a new table, but I
can't get it to work with existing tables.

1) My attempt:
ALTER TABLE project ALTER COLUMN companyID SET DEFAULT NULL;
ALTER TABLE project ADD CONSTRAINT company_is_ta companyID
REFERENCES company(companyID);
(plus variations on the above, resulting in errors, all similar to:)
ERROR: parser: parse error at or near "companyID" at character 53

2) based on this previous posting:

From: Manfred Koizar (mkoi-pg@aon.at)
Subject: Re: NULL Foreign Key
Newsgroups:comp.databases.postgresql.general,
comp.databases.postgresql.questions
Date: 2002-07-17 05:51:19 PST

On Tue, 16 Jul 2002 17:10:32 -0700, "Kuhn, Dylan K (NDTI)"
<KuhnDK@navair.navy.mil> wrote:

Can I make a foreign key that is allowed to be NULL?

Yes:

fred=# CREATE TABLE father (i INT PRIMARY KEY);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'father_pkey' for table 'father'
CREATE
fred=# CREATE TABLE son (i INT REFERENCES father);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
fred=# INSERT INTO father VALUES (1);
INSERT 183317 1
fred=# INSERT INTO son VALUES (1);
INSERT 183318 1
fred=# INSERT INTO son VALUES (2);
ERROR: <unnamed> referential integrity violation - key referenced
from son not found in father
fred=# INSERT INTO son VALUES (NULL);
INSERT 183320 1

Servus
Manfred

Anyone know how I can get this to work? BTW I don't want to use 'ignore'
rules when someone attempts to delete the company as I want the
constraint message to be shown in the app's browser.

TIA
Ron

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Thu, 23 Oct 2003 16:02:03 GMT, Ron <rstpATlin@uxwav.esDOTcom>
wrote:

ALTER TABLE project ADD CONSTRAINT company_is_ta companyID
REFERENCES company(companyID);
(plus variations on the above, resulting in errors, all similar to:)
ERROR: parser: parse error at or near "companyID" at character 53

ALTER TABLE project ADD CONSTRAINT company_is_ta
FOREIGN KEY (companyID) REFERENCES company(companyID);
^^^^^^^^^^^^^ ^

Servus
Manfred

Karen & Manfred, I can get this to work if I set it up when I create new
tables, but I need to change an existing database and it doesn't work
(perhaps a BUG?). When I try the following with my current database I
get an error:
giibdb=# ALTER TABLE project ADD CONSTRAINT company_is_ta FOREIGN
KEY (companyID) REFERENCES tblCompanies(companyID);
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN
KEY check(s)
ERROR: company_is_ta referential integrity violation - key
referenced from project not found in company

Is there a way I can modify an existing database to get the same
end-result (eg it works when DB is set up, before it is populated with
data)?

Ron

Karen Grose wrote:

On Fri, 24 Oct 2003 18:19:05 GMT, Ron <rstpATlin@uxwav.esDOTcom>
wrote:

When I try the following with my current database I
get an error:
giibdb=# ALTER TABLE project ADD CONSTRAINT company_is_ta FOREIGN
KEY (companyID) REFERENCES tblCompanies(companyID);
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN
KEY check(s)
ERROR: company_is_ta referential integrity violation - key
referenced from project not found in company

Is there a way I can modify an existing database to get the same
end-result (eg it works when DB is set up, before it is populated with
data)?

Ron, you can have referential integrity or you can have projects
referencing nonexistent companies, but not both. Whichever you
implement first prevents creation of the other one.

CREATE TABLE company (
companyId int PRIMARY KEY,
name text
);

INSERT INTO company VALUES (1, 'one');
INSERT INTO company VALUES (2, 'two');

CREATE TABLE project (
projectId int PRIMARY KEY,
name text,
companyId int
);

INSERT INTO project VALUES (1, 'p1c1', 1);
INSERT INTO project VALUES (2, 'p2c1', 1);
INSERT INTO project VALUES (3, 'p3', NULL);

-- this works:
ALTER TABLE project ADD CONSTRAINT company_is_ta
FOREIGN KEY (companyId) REFERENCES company (companyId);
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)

-- this will fail:
INSERT INTO project VALUES (4, 'p4c7', 7);
ERROR: company_is_ta referential integrity violation -
key referenced from project not found in company

-- now the other way round:
ALTER TABLE project DROP CONSTRAINT company_is_ta;
INSERT INTO project VALUES (4, 'p4c7', 7);
ALTER TABLE project ADD CONSTRAINT company_is_ta
FOREIGN KEY (companyId) REFERENCES company (companyId);
ERROR: company_is_ta referential integrity violation -
key referenced from project not found in company

To find projects violating the constraint:

SELECT * FROM project AS p WHERE NOT companyId IS NULL
AND NOT EXISTS (
SELECT * FROM company AS c WHERE c.companyId = p.companyId);

Servus
Manfred