sql server - How to handle nullable primary key that references two tables

Question

I have a database with three tables, call them foo, bar and baz. Each instance of bar references an instance of baz, although there may be instances of baz not referenced in the bar table. In most cases, an instance of foo will reference an instance of bar (and therefore can be mapped to an instance of baz.) However in some cases an instance of foo will reference the baz table directly.

In a simplified version where the foo-baz relationship doesn't exist, I can easily make barid part of the primary key for the foo table, which is what I'd like to do here, except that barid will occasionally be null.

Therefore a sample from the foo table might look like this:

fooid    barid    bazid
    1        1     NULL
    2        2     NULL
    3     NULL        9
    4       17     NULL

The issue is how to handle creating a primary key for the foo table. Should I be using unique indexes instead of a constraint? I could also create dummy instances of bar for the purpose of connecting the foo and baz tables. In that case I might add an isreal field so I know when this is the case. I'm sure someone can tell me why this is a bad idea though.

Answer 1

There is not straightforward answer to this question without understanding the application and the implementation.Please see below the other permutations created for relations amongst the table.

fooid    barid    bazid
    1        1     NULL
    2        2     NULL
    3     NULL        9
    4       17     NULL
    5       18     6
    6       14     6
    6       18     10

Generic answer to the question is please do not create dummy values , instead the bar id primary key will be the fooid only. And add a unique constraint if the fooid,barid and bazid is always going to be unique.