Avoid circular reference when two columns in one table point to primary key in another table

Question

I have two tables. First is Users table and second is Referrals table. Referrals table looks like this:

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

The requirement is that if Person B (ID: 2) is referred by Person A (ID: 1), we should not be able to insert the other way around. In other words, the table should not have data like this:

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

Is it possible to check this during insert on database level. I'm using mysql. I read a bit about mysql CHECK constraint here, but cannot figure out how to implement this.

I'm using django ORM and here is what I have tried but failed.

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')
        ]

If it is not possible with Django ORM but possible in database level with some query, that's fine also. However, +1 for django solution. :)

Thank you.

score 1 · Accepted Answer · answered Apr 05 '20 at 13:37

You can't make a CHECK constraint that references other rows. It must be evaluated within a single row.

But you could make a CHECK constraint that requires that the lesser id value be in the first column.

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.

Then you can prevent more than one referral between two people by putting a UNQIUE KEY on those two columns.

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

If the CHECK constraint requires the first number to be the lesser, and the UNIQUE KEY constraint makes sure the same two numbers don't appear on another row already, that will prevent the same pair of people from appearing a second time.

You would also want a column in the table to designate which of the two people is the referring and which one is the referred.

Note that CHECK constraints are only supported in MySQL 8.0.16 and later. If you use an earlier version of MySQL, they can be simulated with a trigger.

Thank you. Its fixed after I upgraded to mysql 8.0.19 – DrGeneral Apr 06 '20 at 07:17 — DrGeneral, Apr 06 '20 at 07:17

Avoid circular reference when two columns in one table point to primary key in another table

1 Answers1