database design - Constraints, 3 FKs with One Null, SQL Server 2012, DB Theory Only -

please read disclaimers before down-voting

i have following table.

inventory (inventoryid (pk), locationid (fk), productid (fk), bookid (fk), quantity) 

my current constraints enforce either bookid or productid cannot null given row, , locationid cannot null.

i want constraint enforces following: locationid + productid + bookid must unique. in other words, not want book or product show twice @ same location.

to clear, record, productid or bookid can null, not both, , locationid cannot null.

disclaimers:

1. this question based entirely on personal curiosity. results not in way impact graded assignment. curious

2. anyone answering should experienced enough assume excluded details

3. i looking possible constraint, or similar solution. not looking rework

in sql server, unique constraint/index allows 1 null value. so, think can want filtered index:

create unique index unq_inventory_locationid_productid_bookid     on inventory(locationid, productid, bookid)     locationid not null , productid not null , bookid not null; 

your check constraints should take care of other conditions.

edit:

ah, see. need 2 unique constraints:

create unique index unq_inventory_locationid_productid     on inventory(locationid, productid)     locationid not null , productid not null;  create unique index unq_inventory_locationid_bookid     on inventory(locationid, bookid)     locationid not null , bookid not null; 

or, if prefer 1 unique index, can use computed column:

alter table inventory add bookid_or_productid (coalesce(bookid, productid));  create unique index unq_inventory_locationid_bookid_productid     on inventory(locationid, bookid_or_productid);