Use NULL foreign key or point to a reserved record?

I'm designing a database containing messages and messengers.

create table Message(
    MessageID int,
    MessengerID int,
    Content nvarchar(max)
)
create table Messenger开发者_运维问答(
    MessengerID int,
    MessengerName nvarchar(100)
)

Sometimes the messenger in unknown. Would you use a NULL value in that case, or a reserved record for unknown messengers in the Messenger table? I'd love to see a short explanation why one solution is better than the other.

I would use NULL.

1. This is exactly what NULL exists for. To say "I've no idea what it is."

2. Having a reserved record in a table is always a pain. You now have to say to your users, hey, you can add more messengers if you wish, and you can delete them, and you can even amend, but don't delete this line, and that line, too. This causes unnecessary complications.

Neither. I would use another table for the messenger info:

CREATE TABLE MessageMessenger(
    MessageID int NOT NULL PRIMARY KEY REFERENCES Message (MessageID),
    MessengerID int NOT NULL REFERENCES Messenger (MessengerID));

(I note in passing that ALL your columns are nullable and your tables don't have keys. I would fix that first!)

NULL in SQL does not accurately represent the semantics of something being "unknown". Using it that way frequently leads to contradictions and incorrect results and it isn't necessary if you design tables that accurately model the situation that the database is supposed to represent.

Another reason not to use nullable foreign keys is that different DBMSs disagree on how they work and users probably won't understand them or use them correctly.