mysql - 友谊关系- sql

Question

我在网站上进行了搜索，找到了一些示例，但并非针对我的问题。

我正在用 SQL 创建一个友谊表，我想确保每一行都是唯一的。例如:如果 user x 是 user y 的 friend ---> user y 是 x 的 friend 。我试图让它无法进入(在创建表格时):

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));

Answer 1

我会采取不同的方法。因为哪个用户是 user1id 哪个是 user2id 并不重要，我只是决定 user1id 必须始终是较小的数字(应用程序有责任在插入两个 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)
);