Re: [GENERAL] Foreign Keys in PostgreSQL

Simeo,

Rules, Triggers + functions. A function is supplied - refint? in the
contrib directory or you can write your own as below:

CREATE TABLE Companies (
CompanyId int4,
Text1 varchar(50),
PRIMARY KEY (CompanyId)
);
CREATE TABLE Projects (
ProjectId int4,
DeveloperCompanyId int4,
SponsorCompanyId int4,
Text1 varchar(50),
Text2 varchar(50),
PRIMARY KEY (ProjectId),
FOREIGN KEY (DeveloperCompanyId)
REFERENCES Companies(CompanyId),
FOREIGN KEY (SponsorCompanyId)
REFERENCES Companies(CompanyId)
);

----------------------------------------------------------------------------
----
-- File: create_functions.sql
-- Description: Create functions on the database.
----------------------------------------------------------------------------
----
drop function fn_CompaniesProjects_XUXM1();

create function fn_CompaniesProjects_XUXM1() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER AN UPDATE ON TABLE Companies
(Master)
-- Generated by PgUpT 17/07/1999
--
DECLARE
l_Flag integer;
BEGIN
l_Flag := 0;
-- Check to see if any participating fields have been modified
if new.CompanyId != old.CompanyId then
l_Flag := 1;
end if;
-- If participating fields have been updated then check to see if
-- any slave records exist
if l_Flag > 0 then
--
-- Enforce relationship between Companies and Projects
--
DECLARE
l_s Projects%ROWTYPE;
l_err_text text;
BEGIN
SELECT * FROM Projects INTO l_s
WHERE
SponsorCompanyId = old.CompanyId
;
if found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
return NULL;
else
return new;
end if;
END;

end if;
return new;
END;'
language 'plpgsql';

drop function fn_CompaniesProjects_XXDM1();

create function fn_CompaniesProjects_XXDM1() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER A DELETE ON TABLE Companies
(Master)
-- Generated by PgUpT 17/07/1999
--
--
-- Enforce relationship between Companies and Projects
--
DECLARE
l_s Projects%ROWTYPE;
l_err_text TEXT;
BEGIN
SELECT * FROM Projects INTO l_s WHERE
SponsorCompanyId = old.CompanyId
;
if found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
end if;
return old;
END;'

language 'plpgsql';

drop function fn_CompaniesProjects_XXXS1();

create function fn_CompaniesProjects_XXXS1() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER AN UPDATE ON TABLE Projects
(Slave)
-- Generated by PgUpT 17/07/1999
--
DECLARE
l_m Companies%ROWTYPE;
l_err_text text;
l_Flag integer;
BEGIN
l_Flag := 0;
if new.SponsorCompanyId IS NOT NULL then
l_Flag := 1;
end if;
if l_Flag > 0 then
SELECT * FROM Companies INTO l_m
WHERE
CompanyId = new.SponsorCompanyId
;
if not found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
end if;
return NULL;
else
return new;
end if;
END;'
language 'plpgsql';

drop function fn_CompaniesProjects_XUXM2();

create function fn_CompaniesProjects_XUXM2() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER AN UPDATE ON TABLE Companies
(Master)
-- Generated by PgUpT 17/07/1999
--
DECLARE
l_Flag integer;
BEGIN
l_Flag := 0;
-- Check to see if any participating fields have been modified
if new.CompanyId != old.CompanyId then
l_Flag := 1;
end if;
-- If participating fields have been updated then check to see if
-- any slave records exist
if l_Flag > 0 then
--
-- Enforce relationship between Companies and Projects
--
DECLARE
l_s Projects%ROWTYPE;
l_err_text text;
BEGIN
SELECT * FROM Projects INTO l_s
WHERE
DeveloperCompanyId = old.CompanyId
;
if found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
return NULL;
else
return new;
end if;
END;

end if;
return new;
END;'
language 'plpgsql';

drop function fn_CompaniesProjects_XXDM2();

create function fn_CompaniesProjects_XXDM2() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER A DELETE ON TABLE Companies
(Master)
-- Generated by PgUpT 17/07/1999
--
--
-- Enforce relationship between Companies and Projects
--
DECLARE
l_s Projects%ROWTYPE;
l_err_text TEXT;
BEGIN
SELECT * FROM Projects INTO l_s WHERE
DeveloperCompanyId = old.CompanyId
;
if found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
end if;
return old;
END;'

language 'plpgsql';

drop function fn_CompaniesProjects_XXXS2();

create function fn_CompaniesProjects_XXXS2() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER AN UPDATE ON TABLE Projects
(Slave)
-- Generated by PgUpT 17/07/1999
--
DECLARE
l_m Companies%ROWTYPE;
l_err_text text;
l_Flag integer;
BEGIN
l_Flag := 0;
if new.DeveloperCompanyId IS NOT NULL then
l_Flag := 1;
end if;
if l_Flag > 0 then
SELECT * FROM Companies INTO l_m
WHERE
CompanyId = new.DeveloperCompanyId
;
if not found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
end if;
return NULL;
else
return new;
end if;
END;'
language 'plpgsql';

drop trigger tr_CompaniesProjects_AXUXM1 on Companies;
create trigger tr_CompaniesProjects_AXUXM1 after update on Companies
for each row execute procedure fn_CompaniesProjects_XUXM1();

drop trigger tr_CompaniesProjects_BXXDM1 on Companies;
create trigger tr_CompaniesProjects_BXXDM1 before delete on Companies
for each row execute procedure fn_CompaniesProjects_XXDM1();

drop trigger tr_CompaniesProjects_AXUXS1 on Projects;
create trigger tr_CompaniesProjects_AXUXS1 after update on Projects
for each row execute procedure fn_CompaniesProjects_XXXS1();

drop trigger tr_CompaniesProjects_AAXXS1 on Projects;
create trigger tr_CompaniesProjects_AAXXS1 after insert on Projects
for each row execute procedure fn_CompaniesProjects_XXXS1();

drop trigger tr_CompaniesProjects_AXUXM1 on Companies;
create trigger tr_CompaniesProjects_AXUXM1 after update on Companies
for each row execute procedure fn_CompaniesProjects_XUXM1();

drop trigger tr_CompaniesProjects_BXXDM1 on Companies;
create trigger tr_CompaniesProjects_BXXDM1 before delete on Companies
for each row execute procedure fn_CompaniesProjects_XXDM1();

drop trigger tr_CompaniesProjects_AXUXS1 on Projects;
create trigger tr_CompaniesProjects_AXUXS1 after update on Projects
for each row execute procedure fn_CompaniesProjects_XXXS1();

drop trigger tr_CompaniesProjects_AAXXS1 on Projects;
create trigger tr_CompaniesProjects_AAXXS1 after insert on Projects
for each row execute procedure fn_CompaniesProjects_XXXS1();
--
drop trigger tr_CompaniesProjects_AXUXM2 on Companies;
create trigger tr_CompaniesProjects_AXUXM2 after update on Companies
for each row execute procedure fn_CompaniesProjects_XUXM2();

drop trigger tr_CompaniesProjects_BXXDM2 on Companies;
create trigger tr_CompaniesProjects_BXXDM2 before delete on Companies
for each row execute procedure fn_CompaniesProjects_XXDM2();

drop trigger tr_CompaniesProjects_AXUXS2 on Projects;
create trigger tr_CompaniesProjects_AXUXS2 after update on Projects
for each row execute procedure fn_CompaniesProjects_XXXS2();

drop trigger tr_CompaniesProjects_AAXXS2 on Projects;
create trigger tr_CompaniesProjects_AAXXS2 after insert on Projects
for each row execute procedure fn_CompaniesProjects_XXXS2();

drop trigger tr_CompaniesProjects_AXUXM2 on Companies;
create trigger tr_CompaniesProjects_AXUXM2 after update on Companies
for each row execute procedure fn_CompaniesProjects_XUXM2();

drop trigger tr_CompaniesProjects_BXXDM2 on Companies;
create trigger tr_CompaniesProjects_BXXDM2 before delete on Companies
for each row execute procedure fn_CompaniesProjects_XXDM2();

drop trigger tr_CompaniesProjects_AXUXS2 on Projects;
create trigger tr_CompaniesProjects_AXUXS2 after update on Projects
for each row execute procedure fn_CompaniesProjects_XXXS2();

drop trigger tr_CompaniesProjects_AAXXS2 on Projects;
create trigger tr_CompaniesProjects_AAXXS2 after insert on Projects
for each row execute procedure fn_CompaniesProjects_XXXS2();

-----Original Message-----
From: Simeó <simeo(at)nil(dot)fut(dot)es>
To: pgsql-general(at)postgreSQL(dot)org <pgsql-general(at)postgreSQL(dot)org>
Date: 15 July 1999 13:24
Subject: [GENERAL] Foreign Keys in PostgreSQL

>How can I implement foreign Keys with postgres? thanks.
>
>
>
> ----------------
> Simeó Reig
> simeo(at)tinet(dot)org
> Barcelona (SPAIN)
> -----------------
>