Re: Nullable 'Foreign Key-like' Constraint

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