By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,708 Members | 2,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,708 IT Pros & Developers. It's quick & easy.

Nullable 'Foreign Key-like' Constraint

P: n/a
Ron
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 (mk*****@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)"
<Ku****@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

Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.