Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

 
 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

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