how to create a Foreign-Key constraint to a subset of the rows of a table?

foreign-keys, postgresql, sql

Solution

I think your schema should be something like this

create table order_nature (
    nature_id int primary key,
    description text
);
insert into order_nature (nature_id, description)
values (1, 'sale'), (2, 'buy')
;

create table order_type (
    type_id int primary key,
    description text
);
insert into order_type (type_id, description)
values (1, 'type 1'), (2, 'type 2')
;

create table order_nature_type (
    nature_id int references order_nature (nature_id),
    type_id int references order_type (type_id),
    primary key (nature_id, type_id)
);

insert into order_nature_type (nature_id, type_id)
values (1, 1), (1, 2), (2, 1), (2, 2)
;

create table sale_information (
    nature_id int default 1 check (nature_id = 1),
    type_id int,
    foreign key (nature_id, type_id) references order_nature_type (nature_id, type_id)
);

If the `foreign key` clause would also accept an expression the sale information could omit the nature_id column

create table sale_information (
    type_id int,
    foreign key (1, type_id) references order_nature_type (nature_id, type_id)
);

Notice the `1` in the `foreign key`

Problem

I have a reference table, say `OrderType` that collects different types of orders: ``` CREATE TABLE IF NOT EXISTS OrderType (name VARCHAR); ALTER TABLE OrderType ADD PRIMARY KEY (name); INSERT INTO OrderType(name) VALUES('sale-order-type-1'); INSERT INTO OrderType(name) VALUES('sale-order-type-2'); INSERT INTO OrderType(name) VALUES('buy-order-type-1'); INSERT INTO OrderType(name) VALUES('buy-order-type-2'); ``` I wish to create a FK constraint from another table, say `SaleInformation`, pointing to that table (`OrderType`). However, I am trying to express that not all rows of `OrderType` are eligible for the purposes of that FK (it should only be sale-related order types). I thought about creating a view of table `OrderType` with just the right kind of rows (view `SaleOrderType`) and adding a FK constraint to that view, but PostgreSQL balks at that with: ``` ERROR: referenced relation "SaleOrderType" is not a table ``` So it seems I am unable to create a FK constraint to a view (why?). Am I only left with the option of creating a redundant table to hold the sale-related order types? The alternative would be to simply allow the FK to point to the original table, but then I am not really expressing the constraint as strictly as I would like to.

Original source