CODEWITHSUNDEEP
asp.netsql
JamesP
JamesP

Reputation: 195

change foreign key so that it points to another table

I've been going around the net and I just find the solutions or explanations to be convoluted, with dropping and adding constraints and such. First off, I have a foreign key in my Transaction table that points to a Users table which has UserName as its primary key, because the Transactions table has to record which user added or modified which record. But now the specs of the program has changed because I ended up utilizing the table provided by ASP.Net, the aspnet_Users table. I am now going to point that foreign key to the UserID primary key found in aspnet_Users. How do I do it properly?

Upvotes: 0

Views: 70

Answers (1)

Nico
Nico

Reputation: 12683

JamesP,

It would be much more helpful if you were able to provide some schema information. However I suspect that your Users table Username is a varchar datatype and the UserID is a uniqueidentifier datatype. Because of this you will also need to make sure you have the UserID field on the Transaction table as a uniqueidentifier datatype. If this has been done and all your affected rows have been updated accordingly you can simply modify the foreign key relation property in the management studio using the Relationships function.

If you wanted to do this purely in SQL you will need to dropthe existing relationship such as (psudo code)

ALTER TABLE [Transaction] DROP CONSTRAINT [<constraintName>]

The recreate the constraint.

ALTER TABLE [Transaction] ADD CONSTRAINT [<constraintName>] FOREIGN KEY([UserID]) REFERENCES [aspnet_Users](UserID)

Cheers.

Upvotes: 1

Related Questions