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

Question

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.

Answer 1

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