I have a table which holds data, and one of those rows needs to exist in another table. So, I want a foreign key to maintain referential integrity.
CREATE TABLE table1
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
AnotherID INT NOT NULL,
SomeData VARCHAR(100) NOT NULL
)
CREATE TABLE table2
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
AnotherID INT NOT NULL,
MoreData VARCHAR(30) NOT NULL,
CONSTRAINT fk_table2_table1 FOREIGN KEY (AnotherID) REFERENCES table1 (AnotherID)
)
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?
Best Answer
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:
So in your case if you make
AnotherID
unique, it will be allowed. If you can't apply a unique constraint you're out of luck, but this really does make sense if you think about it.Although, as has been mentioned, if you have a perfectly good primary key as a candidate key, why not use that?