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@nil.fut.es>
To: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.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@tinet.org
>          Barcelona   (SPAIN)
>          -----------------
>