[GENERAL] Nullable 'Foreign Key-like' Constraint

[Ron]

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 ([EMAIL PROTECTED])
> 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)"
> <[EMAIL PROTECTED]> 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