By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(100) NOT NULL,
department_type VARCHAR(100) NOT NULL,
flagTypeA int GENERATED ALWAYS
AS( case when department_type='Type A' then 1 else 0 end) stored
,constraint depTypeA unique (department_id,flagTypeA)
);
insert into departments (department_name,department_type)values
('Department-1','Type A')
,('Department-2','Type B')
,('Department-3','Type A')
;
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INT,
flagTypeA int GENERATED ALWAYS AS( 1) stored,
CONSTRAINT fk_department_typeA FOREIGN KEY(department_id,flagTypeA)
references departments(department_id, flagTypeA )
);
select * from departments;
CREATE TABLE
INSERT 0 3
CREATE TABLE
| department_id | department_name | department_type | flagtypea |
|---|---|---|---|
| 1 | Department-1 | Type A | 1 |
| 2 | Department-2 | Type B | 0 |
| 3 | Department-3 | Type A | 1 |
SELECT 3
insert into employees (employee_name,department_id) values
('Employee-1',1)
;
select * from employees;
INSERT 0 1
| employee_id | employee_name | department_id | flagtypea |
|---|---|---|---|
| 1 | Employee-1 | 1 | 1 |
SELECT 1
insert into employees (employee_name,department_id) values
('Employee-2',2)
;
select * from employees;
ERROR: insert or update on table "employees" violates foreign key constraint "fk_department_typea" DETAIL: Key (department_id, flagtypea)=(2, 1) is not present in table "departments".
insert into employees (employee_name,department_id) values
('Employee-3',3)
;
select * from employees;
INSERT 0 1
| employee_id | employee_name | department_id | flagtypea |
|---|---|---|---|
| 1 | Employee-1 | 1 | 1 |
| 3 | Employee-3 | 3 | 1 |
SELECT 2