NHibernate Mapping a Table Were the Primary Key is Also a Foreign Key

I have 2 tables as follows:

create table Users
(
 UserId int primary key identity not null
)

create table UserExternalKeys
(
 UserIdRef int primary key not null,
 ExternalKey varchar(50) unique not null
)

alter table UserExternalKeys
add constraint fk_UsersExternalKeys_Users
foreign key (UserIdRef)
references Users (UserId)

Each user can have a 0 or 1 external keys. Things are setup this way because adding a nullable unique column to SQL Serv开发者_JAVA百科er does not allow for more than 1 null value.

Based on Ayende's post, it seems like this could be handled using a <one-to-one> mapping. However, this would require the UserExternalKeys table to have its own primary key.

The new schema would look something like this:

create table Users
(
    UserId int primary key identity not null,
    ExternalKeyRef int null
)

create table UserExternalKeys
(
    UserExternalKeyId int primary key identity not null,
    ExternalKey varchar(50) unique not null
)

alter table Users
add constraint fk_Users_UsersExternalKeys
foreign key (ExternalKeyRef)
references UserExternalKeys (UserExternalKeyId)

I think this would work, but it feels like I would only be adding the UserExternalKeyId column to appease NHibernate.

Any suggestions?

If a user can have 0 or 1 external keys why not design the tables as:

create table Users
(
    UserId int primary key identity not null
    ExternalKey varchar(50) null
)

and use one of the known workarounds for this problem. If you're using SQL Server 2008 you can use a filtered index. If you're using an earlier version you can use a trigger, an indexed view (2005), or the nullbuster workaround.

You could also keep your original schema and map the relationship as one-to-many from Users to UserExternalKeys. Map the collection as a private member and expose access to it through a property:

private IList<UserExternalKeys> _externalKeys;

public string ExternalKeys
{
    get
    {
        if (_externalKeys.Count() == 1) 
        {
            return _externalKeys.ElementAt(0).ExternalKey;
        }
        else
        {
            // return null or empty string if count = 0, throw exception if > 1
        }
    }
    set
    {
        if (_externalKeys.Count() == 0) { // add key and set value }
        else { // set value if count = 1, throw exception if > 1 } 
    }
}