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

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:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>