Geek Answers Handbook

How to create a foreign key constraint for a subset of table rows?

I have a lookup table, say OrderType , which 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 want to create an FK constraint from another table, say SaleInformation , pointing to this table ( OrderType ). However, I am trying to express that not all OrderType are suitable for the purposes of this FK (these should only be types associated with the sale).

I was thinking of creating an OrderType table OrderType with only the correct row type (view SaleOrderType ) and adding an FK constraint to this view, but PostgreSQL stops with this:

 ERROR: referenced relation "SaleOrderType" is not a table

So it seems that I cannot create an FK constraint for the view (why?). Am I left with only the possibility of creating a redundant table to store types of orders related to the sale? An alternative would be to simply let FK point to the source table, but then I really don't express the restriction as strictly as I would like.

+6
source share
2 answers

I think your circuit 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 also accepts, the sale information may 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) );

Pay attention to 1 in foreign key

+6
source

You can use FK for OrderType to provide referential integrity and a separate CHECK constraint for restricting order types.

If your OrderType values OrderType really structured, then a simple CHECK is enough:

 check (c ~ '^sale-order-type-')

where c is the column of the order type in SaleInformation

If the types are not structured in this way in reality, then you can add some type of flag to OrderType (say, the boolean column is_sales ), write a function that uses this flag to determine if the order type is a sales order:

 create or replace function is_sales_order_type(text ot) returns boolean as $$ select exists (select 1 from OrderType where name = ot and is_sales); $$ language sql

and then use this in CHECK:

 check(is_sales_order_type(c))

Of course, you should not use the is_sales boolean flag, you may have more structure than is_sales . This is just for illustrative purposes.

+1
source

More articles:


All Articles