CODEWITHSUNDEEP
sqlpostgresqltriggers
res1
res1

Reputation: 3660

Postgresql create trigger for integrity constraint in insert / update

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

Upvotes: 0

Views: 260

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247445

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.

Upvotes: 1

Related Questions