对继承的表使用触发器来替换外键 [英] Use triggers on inherited tables to replace foreign keys

问题描述

我刚刚接触PostgreSQL。我有这样的表：

  CREATE TABLE Person（
 ID SERIAL PRIMARY KEY，
 Name VARCHAR ）NOT NULL DEFAULT''，
姓氏VARCHAR（32）NOT NULL DEFAULT''，
生日DATE，
性别VARCHAR（8）
）; 
 
  - 学生表继承人
 CREATE TABLE学生（
 ID_学生序列主键，
 MajorDept VARCHAR（32），
）INHERITS（Person ）; 
 
  - 学生表继承人
 CREATE TABLE Employee（
 ID_Employee SERIAL PRIMARY KEY，
位置VARCHAR（32），
 Rank VARCHAR（32 ），
工资NUMERIC（12,2）
）INHERITS（Person）; 
 
  - 地址表引用人
 CREATE TABLE地址（
 ID_Address SERIAL PRIMARY KEY，
 Person_id INTEGER REFERENCES个人（ID）NOT NULL，
电子邮件VARCHAR（32）UNIQUE，
国家VARCHAR（32），
 CityCode INTEGER，
城市VARCHAR（32），
 AddressLine VARCHAR（60），
）; 
  

根据这些表，当我想将数据插入到地址表，Postgres给出了这个错误：

错误：插入或更新表地址违反外键
约束address_person_id_fkey细节：Key（person_id）=（1）是
不存在于表person。

我已经了解到，在Postgres中

索引（包括唯一约束）和外键约束
仅适用于单个表，不是他们的继承孩子。

我的问题是如何使用触发器来解决这个问题？示例代码将非常有用。

在子表中插入几行后，我可以使用SELECT * FROM Person;查看数据。看起来像：

个人表

  1;Bill;Smith;1985-05-10;male
 2;Jenny;Brown;1986-08-12 ;女
 3;鲍勃;摩根;1986-06-11;男
 4;Katniss;Everdeen;1970-08-12 ;女
 5;Peter;Everdeen;1968-08-12;男
  

学生表

  1 ;比尔;史密斯;1985-05-10;男; 1;化学
 2;珍妮;布朗;1986-08-12 ; 2;physics
 3;Bob;Morgan;1986-06-11;male; 3;physics
  / pre> 
 
 
  员工表  
  4;Katniss;Everdeen;1970-08-12;女; 1;Prof;1; 3500.00 
 5;Peter Everdeen;1968-08-12;男; 2;辅助教授;5; 1800.00 
  
 
 
解决方案
首先用这样的东西摆脱FK：
  alter table address drop constraint address_person_id_fkey 
  
如果抱怨没有一个 address_person_id_fkey 约束然后使用 \d地址; 在 psql 中找出什么FK被调用。
 
 
 
然后一个简单的触发器应该这样做：
 code>创建或替换函数pseudo_fk_for_address（）返回触发器为$$ 
 begin 
如果不存在（从id为new.person_id的人员中选择1）然后
 raise异常'否这样的人：％'，new.person_id; 
 end if; 
返回新; 
 end; 
 $$ language plpgsql; 
  
并附加如下：
 在每个行的地址
上插入或更新之前，创建触发器pseudo_fk_for_address_trigger执行过程pseudo_fk_for_address（）; 
  
然后，如果您尝试为没有'存在于个人（包括从中继承的表）：
  playpen =>插入地址（person_id，email，country，citycode，city，addressline）值（3，'ab'，'b'，2，'c'，'d'）; 
错误：没有这样的人：3 
  
你想添加一个BEFORE DELETE触发器到人以避免悬挂引用，那个基本结构将几乎相同。您可能需要在 address.person_id 上的索引来帮助支持BEFORE DELETE触发器。
 
 
 
引用：
 
 
 
 
 
  PL / pgSQL  
 
 
  PL / pgSQL触发器程序 
 
 
   RAISE   
 
 
 
I'm new to PostgreSQL. I have tables like:
CREATE TABLE Person (
  ID SERIAL PRIMARY KEY,
  Name VARCHAR(32) NOT NULL DEFAULT '',
  Surname VARCHAR(32) NOT NULL DEFAULT '',
  Birthday DATE,
  Gender VARCHAR(8)
);

-- Student table inherits from person
CREATE TABLE Student (
  ID_Student SERIAL PRIMARY KEY,
  MajorDept VARCHAR(32),
) INHERITS(Person);

-- Student table inherits from person
CREATE TABLE Employee (
  ID_Employee SERIAL PRIMARY KEY,
  Position VARCHAR(32),
  Rank VARCHAR(32),
  Salary NUMERIC(12,2)
) INHERITS(Person);

-- Address table references person
CREATE TABLE Address (
  ID_Address SERIAL PRIMARY KEY,
  Person_id INTEGER REFERENCES Person(ID) NOT NULL,
  Email VARCHAR(32) UNIQUE,
  Country VARCHAR(32),
  CityCode INTEGER,
  City VARCHAR(32),
  AddressLine VARCHAR(60),
);
According to these tables, when I want to INSERT data into Adress table, Postgres gives that error:

  
ERROR:  insert or update on table "address" violates foreign key
  constraint "address_person_id_fkey" DETAIL:  Key (person_id)=(1) is
  not present in table "person".
I've learned that in Postgres

  
indexes (including unique constraints) and foreign key constraints
  only apply to single tables, not to their inheritance children.
My question is how can I fix this with using triggers? Sample code would be very useful.

After inserting a few rows to child tables, I can see the data with 'SELECT * FROM Person;' as well. It looks like:

Person Table 
1;"Bill";"Smith";"1985-05-10";"male"
2;"Jenny";"Brown";"1986-08-12";"female"
3;"Bob";"Morgan";"1986-06-11";"male"
4;"Katniss";"Everdeen";"1970-08-12";"female"
5;"Peter";"Everdeen";"1968-08-12";"male"
Student Table
1;"Bill";"Smith";"1985-05-10";"male";1;"chemistry"
2;"Jenny";"Brown";"1986-08-12";"female";2;"physics"
3;"Bob";"Morgan";"1986-06-11";"male";3;"physics"
Employee Table
4;"Katniss";"Everdeen";"1970-08-12";"female";1;"Prof";"1";3500.00
5;"Peter";"Everdeen";"1968-08-12";"male";2;"Assist-Prof";"5";1800.00

解决方案
First get rid of the FK with something like this:
alter table address drop constraint address_person_id_fkey
If that complains about there not being an address_person_id_fkey constraint then use \d address; in psql to find out what the FK is called.

Then a simple trigger like this should do the trick:
create or replace function pseudo_fk_for_address() returns trigger as $$
begin
    if not exists(select 1 from person where id = new.person_id) then
        raise exception 'No such person: %', new.person_id;
    end if;
    return new;
end;
$$ language plpgsql;
And attach it like this:
create trigger pseudo_fk_for_address_trigger before insert or update on address 
for each row execute procedure pseudo_fk_for_address();
Then you'll get an error like this if you try to add an address for someone that doesn't exist in person (including the tables that inherit from it):
playpen=> insert into address (person_id, email, country, citycode, city, addressline) values (3, 'ab', 'b', 2, 'c', 'd');
ERROR:  No such person: 3
You'd want to add a BEFORE DELETE trigger to person to avoid dangling references, that basic structure would be pretty much the same. You might want an index on address.person_id to help support the BEFORE DELETE trigger as well.

References:


PL/pgSQL
PL/pgSQL Trigger Procedures
RAISE


                        
这篇关于对继承的表使用触发器来替换外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！