uniqueidentifier cannot be used twice as FK?!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

uniqueidentifier cannot be used twice as FK?!

Author

Topic

basbomer
Starting Member

5 Posts

Posted - 2007-07-25 : 05:38:38

I'm trying to set up an alternative way of creating user_profiles with asp.net 2.0. The reason to not use aspnet_profiles is that I want to be able to use userproperty-fields inside Stored procedures for multiple users at once. It seems to be hard (too hard for me!) to get it to work with the aspnet_profiles table.

Instead of the aspnet_Profiles table I'm using two custom userproperty-tables (tbl_User_properties, tbl_EmployeeProperties) which I want to have a one-to-one relationship with aspnet_Users.userid which is of type uniqueidentifier. In MS SQL server management studio express I'm trying to create a one-to-one relationship for each of the above mentioned 2 property tables with the aspnet_users table. Both primary-key fields in both the property tables are of type uniqueidentifier (as is the aspnet_user.userid collumn). I can create a one-to-one relationship for each one of the property tables, however not for both. SQL server throws me a conflict in the foreignkey constraint ("unable to create relationship '__x__' ALTER-table instruction has led to a conflict in the foreign-key constraint of '__X__', etc, etc).

I hope someone has a solution to my problem. I wouldn't like to just use one property table and get my DB a bit unnormalized...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-25 : 05:42:31

well if you're sure that it's always a one to one relationship then why would just one table be unnormalized?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

basbomer
Starting Member

5 Posts

Posted - 2007-07-25 : 05:51:40

Because some properties will never apply for certain users. Or to be more specific EMployees have extra properties(EmployeeID, room-no, work-telephone, etc) that will never be registered for Clients.

pootle_flump

1064 Posts

Posted - 2007-07-25 : 05:51:54

You should be generating primary keys in only one table (the master) and the foreign keys that reference this should be the same data type but not identity fields.

pootle_flump

1064 Posts

Posted - 2007-07-25 : 05:54:09

quote:
Originally posted by basbomer

Because some properties will never apply for certain users. Or to be more specific EMployees have extra properties(EmployeeID, room-no, work-telephone, etc) that will never be registered for Clients.

That senario will not result in an "unnormalised" design. However, I prefer your design anyway for this sort of problem.

basbomer
Starting Member

5 Posts

Posted - 2007-07-25 : 06:00:31

@Pootle_flump: How to set the FK's not as identity fields but still get a one-to-one relationship? Should I make the uniqueidentifier collumns in these tables not the PK (but maybe UNIQUE) for these tables and leave them PK-less?

Howcome I can use this construct for one "sub-table" and not for two? any ideas?

p.s. Just registered and already think that it has been the best step in my short APP/DB development carreer! Thanks for all your replies!

pootle_flump

1064 Posts

Posted - 2007-07-25 : 06:06:53

quote:
Originally posted by basbomer

Howcome I can use this construct for one "sub-table" and not for two? any ideas?

Easy - just leave columns null for people that they don't apply to.

You should not have any identities in either of the child tables. Their primary key is drawn from the master table and this is their unique identifier (Note - you sound like you are using identity and unique identifier interchangeably when they are not the same thing).

basbomer
Starting Member

5 Posts

Posted - 2007-07-25 : 06:46:29

Ok, so the solution would be to create the two property-tables without any primary key? Both tables have a collumn of datatype uniqueidentifier and get their values from the aspnet_users table.

Can I still enforce uniqueness of the uniqueidentifier collumns so that it does not only depend on my correctly writing stored procedures. WIthout the identity collumn it could be possible to enter the same uniqueidentifier twice in the same table, except for of course the aspnet_users table where it is an identity collumn.

I think I understand the difference between identity and uniqueidentifier (the last one being a datatype, and the first one the behaviour of a collumn).

pootle_flump

1064 Posts

Posted - 2007-07-25 : 06:51:53

Heh heh - nope that's not it. I will knock something up in my lunch hour.

pootle_flump

1064 Posts

Posted - 2007-07-25 : 06:54:59

Perhaps Find "One-to-One Relationships" in this article:
http://r937.com/relational.html

In the diagram, PatientID in tblPatient is an identity and primary key. PatientID in tblConfidential is not an identity and is the primary key. It is also a foreign key referencing PatientID in tblPatient.

Make sense?

pootle_flump

1064 Posts

Posted - 2007-07-25 : 07:49:01

[code]USE tempdb
go

IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'tbl_User_properties') BEGIN
DROP TABLE dbo.tbl_User_properties
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'tbl_EmployeeProperties') BEGIN
DROP TABLE dbo.tbl_EmployeeProperties
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'aspnet_user') BEGIN
DROP TABLE dbo.aspnet_user
END

CREATE TABLE dbo.aspnet_user
(
userid INT IDENTITY(1, 1) NOT NULL
, column_a VARCHAR(10)
, CONSTRAINT pk_aspnet_user PRIMARY KEY CLUSTERED (userid) WITH (FILLFACTOR = 100)
)

CREATE TABLE dbo.tbl_User_properties
(
userid INT NOT NULL
, column_b VARCHAR(10)
, CONSTRAINT pk_tbl_User_properties PRIMARY KEY CLUSTERED (userid) WITH (FILLFACTOR = 100)
, CONSTRAINT fk_tbl_User_properties_aspnet_user FOREIGN KEY (userid) REFERENCES aspnet_user (userid) ON DELETE CASCADE
)

CREATE TABLE dbo.tbl_EmployeeProperties
(
userid INT NOT NULL
, column_c VARCHAR(10)
, CONSTRAINT pk_tbl_EmployeeProperties PRIMARY KEY CLUSTERED (userid) WITH (FILLFACTOR = 100)
, CONSTRAINT fk_tbl_EmployeeProperties_aspnet_user FOREIGN KEY (userid) REFERENCES aspnet_user (userid) ON DELETE CASCADE
)
[/code]3 primary keys, 2 foreign keys and an identity (and a partridge in a pear....)

basbomer
Starting Member

5 Posts

Posted - 2007-07-26 : 03:47:56

Thanks for the above code!

I studied and compared it with my own DB a lot of times. At first I could not find a difference. I tried to build the tables using T-SQL instead of MS SQL Server Management Studio's Table design and DB_diagram feature. It worked at once! When using the edit function in MS SSMS for the problematic table it shows the T-SQL with which the tables are created. It seemed that the primary key had not correctly been set for the referencing tables!

Seems that I should not rely on SMSS's GUI design tools too much :)

By the way: I understand Identity now(Understood it before, but this discussion got me hesitating due to the fact i'm was using the uniqueidentifier datatype). However I'm forced to use uniqueidentifier datatype because aspnet_Users is a table created and used by asp.net own's authentication and authorization methods.

Thanks for all your help!

pootle_flump

1064 Posts

Posted - 2007-07-26 : 12:03:01

quote:
Originally posted by basbomer

Seems that I should not rely on SMSS's GUI design tools too much :)

That is a good lesson to learn

Sorry - I was using unique identifier in a confusing manner too (I am still stuck on the term GUID). I was using it mean anything that can uniqely identify one and only one row in a table. This applies to identity and uniqueidentifier data types!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources