Solved

Creating foreign key relations to ASP.NET Users uniqueidentifer field

Posted on 2010-09-10
7
687 Views
Last Modified: 2012-05-10
Hi Experts

Im sure there is a simple answer to this, but its not something Ive come across before and im a little bit unsure as to how to proceed.

I have a small number of users as part of an admin site that are created and managed by ASP.NET's membership provdier. As such they have been created in SQL Server with the UniqueIdentifier data fields as their identifying ID.

I have several other tables that I need to create a foriegn key relationship to the user the record relates to. usually id just be dealing with Integer values, but what datatype should the foreign key field be? Do I really need to store the uniqueidentifer value in every foreign key record?

Many thanks

Stewart
0
Comment
Question by:digital_soul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 250 total points
ID: 33644710
Basically yes. The UniqueIdentifer is the key field for the aspnet_Users table so is the field you should use to reference foreign tables.
0
 
LVL 2

Assisted Solution

by:icemokka
icemokka earned 250 total points
ID: 33644720
You could add a int field to the user table with an identity seed (which is managed by SQL)
Then you can link that int field to your other tables.

Or you can write your own user management system with an int field
0
 
LVL 2

Author Comment

by:digital_soul
ID: 33644735
Thanks. Is there a strong arguement to take one approach over the other? If I simply reference the uniqueidentifier field in all fireign tables, what datatype should the foreign userId field be?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33644751
> Thanks. Is there a strong arguement to take one approach over the other?
For simplicity and portability (to any asp.net app), link by the uniqueidentifier is a good approach.

> If I simply reference the uniqueidentifier field in all fireign tables, what datatype should the foreign userId field be?
It should be the same, i.e. uniqueidentifier
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33644756
Other than the type, it is created just the same way as with int foreign key constraints.
0
 
LVL 2

Expert Comment

by:icemokka
ID: 33644800
It's kinda a question of personal preference.

There are 2 extra advantages in my approach to my opinion
- Less expensive to store & index an int <-> guid
- An int is much easier to read when you need to stroll though your tables for debugging/troubleshooting

 
0
 
LVL 12

Expert Comment

by:Cyber-spy
ID: 33644831
If the table uses a GUID as the primary key, then this is what you need to use to identify the record in other tables, UNLESS there is another column that UNIQUELY identifies the row, in which case that can be used.

I presume the schema you are using is the one defined here: http://msdn.microsoft.com/en-us/library/aa478949.aspx#data_schema

If so, no other columns stand out as alternative keys. Although the email address would be unique, it too is a long field, and can also change, so you'd need to ensure changes are cascaded to all child tables otherwise the relationship would get broken.

So, what can you do?
Leave as it is, and carry on using the GUID as the key. It will take more space, and you will need to ensure any indexes that use the UserId column (probably most of them!) are regularly defragmented, as UID's cause index fragmentation, particularly in clustered indexes. Although GUID's aren't ideal keys, they are useful in distributed databases where there may be a conflict between integers as identity columns, which is why MS would have used them here. They are not ideal, but do work.

You could try changing the UserId column to an int column, populate it with numbers, then test all the Membership provider methods/properties to see if they work (I suspect they won't) but if they do, you've solved your problem.

Create your own custom Membership Provider (see http://msdn.microsoft.com/en-us/library/f1kyba5e.aspx) which will allow you to define your own columns.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question