Use triggers on inherited tables to replace foreign keys

Question

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

Blinnie

what data exists in Employee, Student, & Person? – Anselme

@kelloti: Doesn't matter that much. FKs and table inheritance don't interact that well in PostgreSQL. If you insert into employee to produce a person.id of 1, that 1 won't be seen an FK that refers to person.id. – Tonnie

@muistooshort - Ok, just a shot in the dark, but is the problem also that Student and Employee also have primary keys (essentially making each of them have two primary keys). I really don't know much about this feature in PostreSQL, but I'd suggest removing primary keys from Student and Employee. – Anselme

Answer 1

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

Tonnie

Answer 2

Foreign keys are not inherited. If the foreign key points to table person, then the same value has to be in that table. The implementation of inheritance is limited in PostgreSQL, I quote from the chapter "Caveats" in the manual:

There is no good workaround for this case.

This includes @Mu's proposed trigger. You would need a lot more than a trigger ON INSERT to guarantee referential integrity. I would not try that. What happens if you delete a person? Change it's ID?

I would consider not to use inheritance at all. If you still want or have to, I would propose several changes to your data model.

• 1) email should not be in the address table, it has nothing to to with an address and everything with the person. Move it to the table person. The reason for the misplacement may be that you want to enforce uniqueness. Another reason not to use inheritance at all.

• 2) The columns id_student and id_employee are redundant. Use the inherited column id as primary keys instead. Just add a constraint to your child tables:

  CONSTRAINT student_pkey PRIMARY KEY (id)
  CONSTRAINT employee_pkey PRIMARY KEY (id)
  

  This also eliminates one of two sources of possible duplicates in the id column over the inheritance tree. (The other one is that you can still enter IDs into student that are present in employee or person. Another caveat in the inheritance system. So, never manually insert or change the id. Leave it to the column default and the sequence.

• 3) The "natural" model would be to have an n:m relationship between address and person. For your model I would implement that with an additional table person_address where address_id references table address and person_id only dreams of a foreign key constraint (original problem).

  The way you have it, an address can never be inhabited by more than one person. Maybe it's good enough for your purpose. This way you might as well imbed the whole address into the person table (and have student and employee inherit it) to avoid your foreign key problem altogether.

Amylopsin