django - Can I create foreign key with specific value match from parent table - Stack Overflow

admin管理员组

文章数量:1359245

Suppose Parent table is

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

Suppose department_type is Type A and Type B.

I want to create Table Employee with foreign key department_id, but department type is always 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)
);

Is it Possible?

Suppose Parent table is

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

Suppose department_type is Type A and Type B.

I want to create Table Employee with foreign key department_id, but department type is always 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)
);

Is it Possible?

• django
• postgresql

Share Improve this question Follow asked Mar 27 at 14:21 user3036282user3036282 1133 bronze badges 1

• Share the corresponding models – willeM_ Van Onsem Commented Mar 27 at 14:26

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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

本文标签： djangoCan I create foreign key with specific value match from parent tableStack Overflow