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

Answers


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

Need Your Help

MySQL Multi-Table Join

mysql join table

I need to modify the following MySQL statement to include information from a third table... but I suck at joins.

Give CSS :after content a class

css

I'm just wondering if it is possible to assign a CSS class to the content added by an :after pseudoelement. Something like this:

Drupal 7 - Run rule on every user triggered by cron

drupal drupal-7 drupal-commerce

Is it possible to run a rule on every user triggered when the cron runs.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.