Referential integrity when two FK columns need to match themselves in the same table? It's possible my design is wrong, or there's simply a better way. I'll use a very simple example:



    --------- dbo.Book----------
   |                            |
   |  BookID int identity (1,1) |
   |  ShelfID int FK            |--
    ----------------------------   |
                                   |
    --------- dbo.Row-----------   |    
   |                            |  |    
 --|  RowID int identity (1,1)  |  |    
|   ----------------------------   |    
|                                  |        
|   -------- dbo.Shelf----------   | 
|  |                            |  |
|  |  ShelfID int identity (1,1)|--
 --|  RowID int FK              |
    ----------------------------


Simple enough.

But what if I wanted to assign a Row without the Shelf? Maybe we know the row but not the shelf just yet:

    --------- dbo.Book----------
   |                            |
   |  BookID int identity (1,1) |
   |  RowID int FK              | &lt;-- New
   |  ShelfID int FK NULL       | 
    ---------------------------- 


Is there a better design method for this? I need to make sure that ShelfID and RowID match up in their own respective parent/child tables. 

What's the best way? A constraint that calls a function? Seems expensive. Different table structure? I could be missing something super simple obvious.

Elaboration: 

If dbo.Shelf has the following value:

[ShelfID]  [RowID]
------------------
 3          1


Then we should expect the dbo.Row table to have a primary key with the value of 1. I want this same referential integrity on the dbo.Book table:

[BookID] [RowID] [ShelfID]
--------------------------
1        1       3


How can I enforce this? Is a function-called constraint the best way? Or is my design wrong?

My requirement is to uphold data integrity if both values are present in the Book table. There must be a correlating relationship in the other two tables. Maybe I've over complicated it and a check constraint that calls a function is the way to go.

I'm aware of being able to set a FK to null. Basically, if ShelfID and RowID both have a value in the main Book table, what is the best way to make sure that RowID and ShelfID match up? ShelfID must exist in the dbo.Shelf table, that one is easy. But the RowID in Book table must be the same parent found in dbo.Shelf.

What is the best way to make sure a ShelfID and RowID don't end up in the Book table that is a false relationship? For example, RowID = 1, and ShelfID = 3, but in the Shelf table, there is no such relationship.
You need two things:

to add an extra foreign key from Book that references Row.

to modify the foreign key from Book to Shelf so it includes RowID and becomes a composite foreign key: (RowID, ShelfID) REFERENCES Shelf (RowID, ShelfID). This is needed in order for the 2 foreign keys to be aligned. Otherwise, you may end up with a Book that references RowID = 1 and ShelfID = 33 while Shelf 33 is referencing RowID = 2.

and additionally - for the the above modification to succeed - you need to add a UNIQUE constraint on Shelf (RowID, ShelfID). Alternatively, you can modify the Shelf primary key.
All the columns will be not nullable, except Book.ShelfID. There is a way to have all columns not null but it would require an additional table.


The tables become:
        --------- dbo.Row-----------    
       |                            |         
     --|  RowID int   PK            |-----    
    |   ----------------------------      ^   
    ^                                     |  
    |                                     |        
    |   -------- dbo.Shelf----------      ^ 
    ^  |                            |--   |
  FK|  |  ShelfID int PK     UQ1    |  |  |
     --|  RowID int      FK  UQ1    |  |  ^
        ----------------------------   ^  |
                                       |  |
                                    FK1|  ^
        --------- dbo.Book----------   ^  |
       |                            |--|  |
       |  BookID int  PK            |     ^FK2
       |  RowID int      FK1 FK2    |-----|
       |  ShelfID int    FK1        |
        ----------------------------