How to create a foreign key to a non primary key?
If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it. From Books Online: A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
Can a foreign key reference a new column?
If there is a fixed set of valid values you could keep them in their own table with a unique/primary key defined upon the column, then both table2.columnB and table1.columnA can be foreign keys referencing the new table.column.
Can a foreign key reference a non unique index?
When this happens, it usually means that two foreign keys are being linked to each other. Often the table that would contain the key as a primary key isn’t even in the schema. Example: Two tables, COLLEGES and STUDENTS, both contain a column called ZIPCODE. We might discover that the relationship is many to many.
Is there a way to create a foreign key to Table I?
However, as you can see, the table I foreign key to, the column isn’t the PK. Is there a way to create this foreign key, or maybe a better way to maintain this referential integrity? If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.
Can a FOREIGN KEY constraint be linked to a primary key constraint?
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. So in your case if you make AnotherID unique, it will be allowed.
Can a primary key be used on more than one column?
This index also permits fast access to data when the primary key is used in queries. If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.