Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 9 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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