Postgresql create trigger for integrity constraint in insert / update

Question

I have these tables on a database on Postgresql:

create table company (
  id bigint generated always as identity primary key,
  name text not null
);

create table employee (
  id bigint generated always as identity primary key,
  name text not null,
  company_id bigint not null references company(id)
);

create table manager_employee(
  id bigint generated always as identity primary key,
  manager_id bigint not null references employee(id),
  employee_id bigint not null references employee(id)
);
create unique index ux1_manager_employee on manager_employee(manager_id, employee_id);
alter table manager_employee add constraint ck1_manager_employee check (manager_id != employee_id);

I want to be sure that when I insert / update a value in manager_employee table both manager_id and employee_id belong to the same company, same company_id.

I think I have to use a trigger to ensure this condition, how can I create it?

Thanks

Laurenz Albe · Accepted Answer · 2021-02-04 07:27:47Z

1

You can do it with constraints:

ALTER TABLE manager_employee ADD company_id bigint;

UPDATE manager_employee me
SET company_id = e.company_id
FROM employee e
WHERE me.employee_id = e.id;

ALTER TABLE manager_employee ALTER company_id SET NOT NULL;

ALTER TABLE employee ADD UNIQUE (company_id, id);

ALTER TABLE manager_employee ADD FOREIGN_KEY (company_id, manager_id)
   REFERENCES employee(company_id, id);

ALTER TABLE manager_employee ADD FOREIGN_KEY (company_id, employee_id)
   REFERENCES employee(company_id, id);

The unique constraint is as redundant as the new column, but is required as a target for the foreign key constraint.

edited Feb 4, 2021 at 7:27

answered Feb 3, 2021 at 20:29

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

res1 Over a year ago

I don't understand the need of ALTER TABLE employee ADD UNIQUE (company_id, id); , id in employee is generated automatically. Can you explain please?

bobflux Over a year ago

postgres will raise an error if there is no unique constraint on the referenced columns.

Collectives™ on Stack Overflow

Postgresql create trigger for integrity constraint in insert / update

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related