Re: Nullable 'Foreign Key-like' Constraint - Mailing list pgsql-general

Ron,
I have done this by adding the attribute to the table with nulls allowed and adding a constraint to the table for the
foreignkey... 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