How do you create a relationship to a non-primary key in SQL Server?

Question

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?

Answer 1

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.