Is there any concern in referencing a unique index from a foreign key table?
Using SQL Server 2008 R2
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 only takes a minute to sign up.Sign up to join this community
FOREIGN KEY constraint can reference a column combination that is either the
PRIMARY KEY or has a
UNIQUE constraint or has a
Copied from MSDN page,
FOREIGN KEY REFERENCES
Is a constraint that provides referential integrity for the data in the column or columns.
FOREIGN KEYconstraints require that each value in the column exists in the corresponding referenced column or columns in the referenced table.
FOREIGN KEYconstraints can reference only columns that are
UNIQUEconstraints in the referenced table or columns referenced in a
UNIQUE INDEXon the referenced table. Foreign keys on computed columns must also be marked
The only difference between the first (primary key) option and the other two is that unique* constraints and indexes can be defined on nullable columns. Primary key columns have to be
* Note that there are some slight differences between these in SQL-Server (unique indexes offer some options that are not available with unique constraints): When should I use a unique constraint instead of a unique index?
For example, (as @gbn points), a unique index can be partial. But then, it can't be referenced by a FK.