Можно ли создать внешний ключ с определенным значением, совпадающим с родительской таблицей

Предположим, что родительской таблицей является

 
 CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    department_type VARCHAR(100) NOT NULL
 );

Предположим, что тип_отдела - это Тип A и Тип B.

Я хочу создать таблицу Employee с внешним ключом department_id, но тип отдела всегда Type A.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_department FOREIGN KEY (department_id, **department_type ='Type A'** )
    REFERENCES departments(department_id)
);

Возможно ли это?

no the given foreign key constraint in your employees table is not possible in SQL.

since you want to ensure that department_id in the employees table always refers to departments with department_type = 'Type A'.

Use a "CHECK"

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_department FOREIGN KEY (department_id)
        REFERENCES departments(department_id),
    CONSTRAINT check_department_type CHECK (
        department_id IN (
            SELECT department_id FROM departments WHERE department_type = 'Type A'
        )
    )
);

many databases (like MySQL) do not support subqueries in CHECK constraints, but PostgreSQL does.

I think, subqueries and other calculations with foreign table, is not possible in constraint or check.

Use genrated column for additional check.
(I think this approach is better than using a BeforeInsert trigger in the employees table)

This column (flagTypeA in examle) in table departments have value 1 for department Type A and 0 for other
and generated as case when department_type='Type A' then 1 else 0 end.

Generated column flagTypeA in table employees always is 1.
So reference from employee(department_id,1) possible for departments(department_id,'Type A').

 CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    department_type VARCHAR(100) NOT NULL,
        -- add generated column flagTypeA
    flagTypeA int GENERATED ALWAYS 
         AS( case when department_type='Type A' then 1 else 0 end) stored,
        -- add unique constraint for external check
    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,
        -- add generated column flagTypeA
    flagTypeA int GENERATED ALWAYS AS( 1) stored,
        -- add constrint 
    CONSTRAINT fk_department_typeA FOREIGN KEY(department_id,flagTypeA) 
         references departments(department_id, flagTypeA )
);

Test data in departments table.
For department_id in(1,3) allowed reference from employees.

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

Try insert valid employee

insert into employees (employee_name,department_id) values
 ('Employee-1',1)
;
employee_id employee_name department_id flagtypea
1 Employee-1 1 1

Next insert violates constraint

insert into employees (employee_name,department_id) values
 ('Employee-2',2)
;

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".

Next - successful insert

insert into employees (employee_name,department_id) values
 ('Employee-3',3)
;
employee_id employee_name department_id flagtypea
1 Employee-1 1 1
3 Employee-3 3 1

fiddle

You can use boolean type generated column

 CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    department_type VARCHAR(100) NOT NULL,
    flagTypeA boolean GENERATED ALWAYS 
         AS( department_type='Type A') stored
   ,constraint depTypeA unique (department_id,flagTypeA)
 );

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    flagTypeA boolean GENERATED ALWAYS AS( true) stored,
    CONSTRAINT fk_department_typeA FOREIGN KEY(department_id,flagTypeA) 
         references departments(department_id, flagTypeA )
);

There constrint violation error message is

ERROR:  insert or update on table "employees" violates foreign key constraint "fk_department_typea"
DETAIL:  Key (department_id, flagtypea)=(2, t) is not present in table "departments".

fiddle

Вернуться на верх