CODEWITHSUNDEEP
databasepostgresqlforeign-keysconstraints
Nishant
Nishant

Reputation: 21934

Foreign key with additional constraints?

There is a table called Item(id, name, cost) to track Items possible and Orders(id, bill_id, item_id, units) which is created to track orders placed where same bill_id means it belongs to a single order.

How to impose an additional constraint in DB that says Item should be "Available" (at that point of time) if it needs be to added as item_id in the Order table?

My preferred schema design is to add a Type column which would have "Available" and "Unavailable" fields. But how can I check the Foreign Key constraint item_id should not only be a Primary Key in Item table, its Type should be Available as well.

Couple of answers using check constraints seems close How to make a foreign key with a constraint on the referenced table in PostgreSQL and Foreign key with additional relationship constraint.

Another schema design which I don't prefer is to have a Table called "Menu" which could have only the Items Available. The problem is that this table is going to very dynamic and transactional in nature and it keeps changing depending on the availability of items. And I am just creating a subset table out of Items depending on its state which doesn't seem to be a nice idea.

Which schema design and approach would you chose?

Use cases are like a retailer wants to make sure all his order are against items that are "Available". Ofcourse its possible to change "Available" to "Unavailable" at a later point of time. Orders can be made to maitain a constraint, but I am not sure if one track historically if it was really available or not from even this column. I don't if RDBMS can store such info.

Upvotes: 4

Views: 492

Answers (1)

mvsagar
mvsagar

Reputation: 2079

AFAIK, I do not think any RDBMS allows one to provide additional constraints along with referential integrity constraints. In your scenario, you should verify if an item is currently available by executing an SQL SELECT statement on Items table and if so add the item to Orders table in a single database transaction. Of course you can have Type or Status column in Items table to check if an item is available. Or you may want to have stock column in the table to check if an item of required quantity/units is available.

You can implement using your idea of having Type column and using check constraint but I think it is not a good idea. In this case you have to have Type column not only in Items table but also in the Orders table. Then have a check constraint on the Orders table as CHECK (Type = 'Available').

There is already a similar question PostgreSQL check constraint for foreign key condition

This is going to solve your problems by using user defined function.

Upvotes: 2

Related Questions