当一个表中的两列指向另一个表中的主键时，避免循环引用

Question

我有两张桌子。第一个是Users表，第二个是Referrals表。推荐表如下所示：

| Referrals table columns | Points to
| ----------------------- | ---
| new_customer_id         | user table's primary key
| referred_by             | user table's primary key

要求是，如果B（ID:2）被A（ID:1）转介，我们不应该以另一种方式插入。换句话说，该表不应包含以下数据：

| new_customer_id | referred_by |
| --------------- | ----------- |
|       2         |      1      | <-- OK
|       1         |      2      | <-- Both people refering each other should not be allowed

在数据库级插入时是否可以检查此项。我正在使用mysql。我读了一些关于mysql检查约束here的内容，但不知道如何实现它

我正在使用django ORM，下面是我尝试过但失败的内容

class Referrals(models.Model):
    customer = models.ForeignKey(get_user_model(), on_delete=models.CASCADE, db_index=True)
    referred_by = models.ForeignKey(get_user_model(), on_delete=models.CASCADE, db_index=True)

    class Meta:
        constraints = [
            models.CheckConstraint(check=models.Q(customer__gt=models.F('referred_by')),
                                   name='referrer_should_come_before_referee')
        ]

如果在Django ORM中不可能，但在数据库级别通过一些查询也可以，那也可以。但是，对于django解决方案，+1.：）

多谢各位

Answer 1

不能创建引用其他行的检查约束。它必须在一行内计算

但是您可以创建一个检查约束，该约束要求较小的id值位于第一列中

mysql> create table referrals ( 
 referral1 int, 
 referral2 int, 
 check (referral1 < referral2)
);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into referrals values (1,2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into referrals values (2,1);
ERROR 3819 (HY000): Check constraint 'referrals_chk_1' is violated.

然后，您可以通过在这两列上放置UNQIUE键来防止两个人之间的多个引用

alter table referrals add unique key (referral1, referral2);

如果CHECK约束要求第一个数字较小，并且UNIQUE KEY约束确保相同的两个数字不会出现在另一行上，那么将防止同一对人再次出现

您还需要表格中的一列来指定两个人中的哪一个是指代人，哪一个是指代人

---

请注意，检查约束仅在MySQL 8.0.16及更高版本中受支持。如果您使用早期版本的MySQL，那么它们可以是simulated with a trigger