Re: Nullable 'Foreign Key-like' Constraint

On Fri, 24 Oct 2003 18:19:05 GMT, Ron <rstpATlin(at)uxwav(dot)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