ASP.NET Membership Provider: Defining a foreign ke

Question

I'd like to setup a foreign key to a default membership table but I'm getting an error when trying to define it. I'm using the default aspnet_Users table and also my own Posts table. I'm trying to setup the tables as follows:

aspnet_Users

• UserId (PK) uniqueidentifier
• UserName nvarchar(256)

Posts

• PostID (PK) int
• UserName (FK -> aspnet_Users.UserName) nvarchar(256)

However, when I try to set this up using the VS 2010 designer, it gives me the following error:

The columns in table 'aspnet_Users' do not match an existing primary key
or unique constraint.

Is this not working because aspnet_Users.UserName isn't a part of the PK for aspnet_Users? I've tried to change the table to include that as part of the PK (I think that makes it a composite key?) but it's telling me to delete the relationships first before I can do it. Being as I don't know what relationships the default membership tables define, I'd rather find out more before going that route.

Answer 1

I think you need to combine it with ApplicationID as well. UserID is not unique except when combined with the application ID.

Answer 2

The error message is fully describing the situation - you're trying to use a non-key field inside a foreign key constraint. You suggest trying to create a composite key to get around this issue, which would work, but why wouldn't you simply use the existing primary key?

The UserID field is a uniqueidentifier PK, so it is already configured for use in a FK. And, you'd be utilizing normalization, so that rather than storing (up to) 256 bytes per Username on each row, you'd only be storing the uniqueidentifier (16 bytes).