Sql-server – Referential integrity when two FK columns need to match themselves in the same table

database-designforeign keyreferential-integritysql server

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.

Best Answer

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

Related Solutions

What model design supports both data store backed and free form entries

Based on the details you've provided I'd say it sounds fine.

Things that may or may not be a problem based on further information.

(1) Even though you say there is no relationship between the two systems and no foreign key, if you were to switch third part systems, you would be left with a bunch of meaningless ids for books that were added from the previous 3rd part system. Is that okay? If not, you may need to add a lookup table of third party systems that can related to the IDs in the Book table so you can later tell which system they came from.

(2) If the only reason for the IDs from the third party system to be there is to check for their existence, maybe a simply IsFrom3rdParty or IsFreeForm boolean column would be sufficient. It would seem to meet all the requirements you lay out here and would be simpler than the ID to implement and would better convey the actual information you have (which is simply that it came from a 3rd party system. You can't guarantee that the ID will relate to anything especially if the backend system has changed)

Otherwise, yes it sounds good.

Sql-server – Do I need a separate Id column for this “mapping” table

If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:

One-To-Many

create table Producer ( id int identity(1, 1) not null primary key clustered, Name varchar(100) not null ) go create table Product ( id int identity(1, 1) not null, Name varchar(100) not null, ProducerId int not null foreign key references Producer(id) ) go

But if there's a chance that this will be a many-to-many relationship, then your best bet would be to use a Join table.

Many-To-Many

create table Producer
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table Product
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table ProductProducer
(
    ProductId int not null foreign key references Product(id),
    ProducerId int not null foreign key references Producer(id)
)
go

-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer 
primary key (ProductId, ProducerId)
go

If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.

Best Answer

Related Solutions

What model design supports both data store backed and free form entries

Sql-server – Do I need a separate Id column for this “mapping” table

Related Question