8

I have a Users table which has two columns a primary key called UserID and another column called UserName.

  • UserID (int) PK
  • UserName (varchar(256)

They are both unique but I decided for reasons to use the UserName as a reference in other tables. So the order table for example has a reference to user by UserName not userid.

  • OrderID
  • UserName

I want to create a relationship between all tables that reference the UserName and the Users table so that I get the cascade update/delete feature of SQL Server.

But SQL Server doesn't allow me to create a relationship on a non primary key column. Is there any way I can get the cascade update/delete feature without changing the users table so that UserName is the primary key and not UserID?

13

Create a unique index or unique constraint on UserName then you can reference it in a FK constraint fine.

Your statement that

Sql Server doesn't allow me to create a relationship on a non primary key column

is incorrect. SQL Server only cares that the column(s) participating in the FK relationship have a unique index defined.

  • 1
    you are correct, my statement was based on the error message i got from sql server when i tried to create the relationship. – Shrage Smilowitz Jan 13 '12 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.