Referential integrity when two FK columns need to match themselves in the same table?

Question

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              | <-- 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.

Answer 1

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        |
        ----------------------------   

Answer 2

In your question, you have asked (among other things):

"Is there a better design method for this?"

I usually start off by drawing an ERD, and only if this has been mulled over and discussed and re-drawn etc, I move on the relational model. However, I know that for some folk, relational models are more meaningful, so I shall stick to that.

As I wrote in my original answer, you seem to have encountered a design problem that requires a hierarchical layout. There seem to be 2 main ways to solve this with a relational database:

(1) We model (and code) a hierarchy of entities (tables), and add as many relationships (foreign key constraints) as needed - this is roughly what you have used. - or - (2) We express the hierarchy of entities (tables) with a recursive relationship where, in your scenario, a LOCATION for a book can contain another location eg a row has a shelf.

I have created 3 layouts, so that you can see various solutions side-by-side. In the diagram we have the following:

LEFT-HAND side: Approach (1): add more constraints and unique keys. The implementation needs a lot of constraints, and in the case of deeper hierarchies we end up coding lots of keys.

CENTRE of diagram: Approach (2): recursive relationship (location contains one or more locations) eg WAREHOUSE contains ROW contains SHELVES

RIGHT-HAND side: same as centre, but in hierarchical form.

For me (personally), the recursive model appears to be a bit clearer in this case, because it describes what the database is supposed to do: keeping track of a book's location. The locations table contains columns for all components of a location. Coming back to one of your original questions:

"But what if I wanted to assign a Row without the Shelf?" - No problem, such a thing can be a location, too (and thus, can be stored in the locations table).

Also, we can add columns to the LOCATIONS table without greater difficulties, if we need more details at a later stage (because the relationship to BOOK is implemented with a single foreign key constraint).

Answer 3

If the FK is nullable it is not enforced until you assign a value. See the Stack Overflow Q & A set null value in a foreign key column?

I believe you can even set cascade delete to null out a FK.

Answer 4

As far as I understand this problem, you have created a hierarchical layout:

ROW -< SHELF -< BOOK (where -< stands for "one to many")

What you could do is: add another 1:m relationship (ie another FK) between ROW and BOOK.

I do not know what the actual user requirements for this database are. However, I'd probably have just 2 entities in my model: BOOK >- LOCATION. (Each LOCATION may contain one or more BOOKS). The "locations" table would have the following columns: LocationID, RowID, ShelfID.

The PK column would be LocationID. The RowID and ShelfID columns could have a UNIQUE constraint, and also one or more CHECK constraints in order to do some range checking when values get inserted. (eg see https://stackoverflow.com/questions/3474291/unique-constraint-on-multiple-columns/3474460)