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

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

 
 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)
);

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

нет, данное ограничение внешнего ключа в вашей таблице employees невозможно в SQL.

поскольку вы хотите убедиться, что department_id в таблице employees всегда относятся к отделам с 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'
        )
    )
);

многие базы данных (например, MySQL) не поддерживают подзапросы в ограничениях CHECK, но PostgreSQL поддерживает.

Я думаю, подзапросы и другие вычисления с внешней таблицей невозможны в constraint или check.

Используйте сгенерированный столбец для дополнительной проверки.
(Я думаю, что этот подход лучше, чем использование триггера BeforeInsert в таблице employees)

Этот столбец (тип флага A в примере) в таблице departments имеет значение 1 для отдела Type A и 0 для других
и генерируется как case when department_type='Type A' then 1 else 0 end.

Сгенерированный флаг столбца типа A в таблице employees всегда равен 1.
Таким образом, ссылка от сотрудника(department_id,1) возможна для отделов(department_id,'Введите 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 )
);

Тестовые данные в таблице departments.
Для идентификатора отдела в(1,3) допустима ссылка из 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

Попробуйте вставить valid сотрудника

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

Следующая вставка нарушает ограничение

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

Далее - успешная вставка

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

скрипка

Вы можете использовать boolean введите сгенерированный столбец

 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 )
);

Есть сообщение об ошибке нарушения ограничений 

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

скрипка

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