Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Foreign Keys in PostgreSQL

From: "Stephen Boyle" <yd80(at)dial(dot)pipex(dot)com>
To: Simeó <simeo(at)nil(dot)fut(dot)es>, <pgsql-general(at)postgreSQL(dot)org>
Subject: Re: [GENERAL] Foreign Keys in PostgreSQL
Date: 1999-07-18 07:53:06
Message-ID: 005601bed0f2$9c39a880$010aa8c0@m0n7y (view raw or flat)
Thread:
Lists: pgsql-general
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)
>          -----------------
>


pgsql-general by date

Next:From: AVU MakerereDate: 1999-07-18 08:21:29
Subject: Postgresql and Postmaster response
Previous:From: Tim JoyceDate: 1999-07-17 20:55:13
Subject: Re: [GENERAL] PostgreSQL status report

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group