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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 13 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. – Useless Hasid Jan 13 '12 at 17:51

Your Answer


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.