我搜索了该网站,发现了一些示例,但并非特定于我的问题。
我正在用SQL创建一个友谊表,我想确保每一行都是唯一的。例如:如果用户x与用户y是朋友--->用户y与x是朋友。
我试图使其无法输入(在创建表时):
user1ID user2ID timeStamp
11 10 01/01/2010 00:00:00
10 11 01/01/2010 00:00:00
我这样写:
CREATE TABLE FriendRelationship(
User1ID INTEGER NOT NULL,
User2ID INTEGER NOT NULL,
**CHECK (User1ID <> User2ID), /*User x cannot be a frien with himself*/
CHECK ((User1ID <> all User2ID) AND (User2ID <> all( User1ID))),
/*if user x friend with user y -> user y is friend with user x */**
FtimeStamp DATETIME NOT NULL,
FOREIGN KEY (User1ID) REFERENCES UserDF(UserID),
FOREIGN KEY (User2ID) REFERENCES UserDF(UserID),
PRIMARY KEY (User1ID, User2ID));
最佳答案
我会采取另一种方法。由于哪个用户user1id
和哪个用户user2id
都无关紧要,所以我只决定user1id
必须始终是较小的数字(插入两个ID时,应用程序有责任正确地对这两个ID进行排序)。完成此操作后,将两者结合使用的主键将确保您没有重复的条目:
CREATE TABLE FriendRelationship (
User1ID INTEGER NOT NULL,
User2ID INTEGER NOT NULL,
FtimeStamp DATETIME NOT NULL,
CHECK (User1ID < User2ID), -- The decision described above
FOREIGN KEY (User1ID) REFERENCES UserDF(UserID),
FOREIGN KEY (User2ID) REFERENCES UserDF(UserID),
PRIMARY KEY (User1ID, User2ID)
);