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

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?

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.

StackExchange Database Administrators Q#10648, answer score: 14