I have a table user
with userID
as the primary key. I have another table called Friends
. In the Friends
table, I have two Users as friends represented by the columns UserID
and FrndID
where both UserID
and FrndID
should be a userID
in table user
.
I want to enforce data integrity. Could I use something like this?
ADD CONSTRAINT `ufd_users_fk` FOREIGN KEY (`userId`, `friendId`)
REFERENCES `users` (`userId`, `userId`) ON DELETE CASCADE ON UPDATE CASCADE;
I want to know is REFERENCES
users(
userId,
userId)
referencing a column multiple times correctly? The reason I am not creating 2 separate constraints, is that both users must exist in table user
.
No, you should create two foreign keys:
ADD CONSTRAINT `ufd_users_fk` FOREIGN KEY (`userId`)
REFERENCES `users` (`userId`)
ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `ufd_users_fk` FOREIGN KEY (`friendId`)
REFERENCES `users` (`userId`)
ON DELETE CASCADE ON UPDATE CASCADE;