'None' or 'Not Used' foreign key when using GUIDs as primary key

Question

We are building an application that may need to be replicated to many servers (I'm hoping not, but there's no way to know if clients will need their own client/server copy until after we release the online product).

Since the number of servers is unknown, I've decided to use GUIDs for any transactional table IDs. Since each client has their own database, I intend to use the NEWSEQUENTIALID() default, and the largest table will add no more than 1.5M rows per year (but on average 15K rows), I don't expect much of a performance problem.

However, I'm not sure how to handle cases where we want the foreign key to indicate 'none selected'. For example, a client has a single admin user. This is set up as a foreign key (login_id) to login.id (a GUID). However, if a client doesn't yet have an admin user, how would we easily set up a "None Selected" key?

In prior applications we used IDENTITY columns, and inserted a dummy entry in most tables with an ID of 0. Is there an accepted approach to providing similar functionality with a GUID?

Answer 1

I might be missing something really obvious here, but can't you just set it to NULL?

Answer 2

Make the foreign key field in the child table NULLable and keep the field NULL when there's no admin user. Magical values like "0" identity columns is a disaster waiting to occur.

Answer 3

You have 3 options:

1) In your referenced table, add the blank value row with a guid of all zeros, then link to this record

2) Just store a null for the blank references

3) Don't have the blank records in the reference table, but store an all zero guid. This may cause problems if joins are done in in the database, or with reports. You'll have to code for this special case.

I'd say 1 and 2 are the only good options.

Answer 4

The only safe way to do this, is set the FK value to NULL. If you need to replicate data between multiple servers, make sure each table that you replicate has GUIDs as their primary keys, foreign key relationships are this way never a problem.

Replication can work very simple this way: replicate inserts and updates first in the order of parent to child table, after that replicate the deleted records from child to parent. (reverse order.)

Hope this helps.