How to make a Django UniqueConstraint that checks fields in a position-independant way?

Using Django Using the ORM
1

I have a Django Model where I’d like to ensure that no duplicate Edge exists where the same pair of Nodes appear as node_a or node_b, in either order.

class Edge(models.Model):
    project = models.ForeignKey(Project, related_name="edges", on_delete=models.CASCADE)
    node_a = models.ForeignKey(Node, related_name="+", on_delete=models.CASCADE)
    node_b = models.ForeignKey(Node, related_name="+", on_delete=models.CASCADE)

    class Meta:
        constraints = [
            UniqueConstraint(
                fields=("project", "node_a", "node_b"), name="unique_edge"
            ),
        ]

This catches if an Edge is made with the same (A, B), but not if the same Nodes are put in reverse (B, A).

The validation function I’m trying to port to Constraints was:

    def validate_unique(self, *args: Any, **kwargs: Any) -> None:
        if self.project.edges.filter(
            Q(node_a=self.node_a_id, node_b=self.node_b_id)
            | Q(node_a=self.node_b_id, node_b=self.node_a_id)
        ).exists():
            raise ValidationError(
                f"Edges must be unique within a project: {self.node_a_id}|{self.node_b_id}"
            )

This validation function validates that the Nodes are unique in both directions: (A, B) or (B, A).

Is there a way to express this within a UniqueConstraint?

Currently targeting Django 4.1. Cross posted from SO.

2

I would expect you to be able to do this using the condition attribute of a UniqueContstraint.

3

Thanks Ken.

The struggle I’m having is that the condition can check conditions between fields in the incoming row itself, but the information I need is really something in the other existing rows (namely, does any other row contain (B, A) for this incoming row’s (A, B).

4

I found this related StackOverflow question

5

Ok, now I understand what you’re asking for here.

No, PostgreSQL does not support constraints dependant upon data other than what is in the current row being inserted or modified.

See the docs at PostgreSQL: Documentation: 15: 5.4. Constraints, particularly the green “Note” boxes at the bottom of section 5.4.1

This is something that you would need to enforce yourself in your code, or else create a database trigger at the database level to do this (but see the caveats about that in those same notes.)

1 Like
6

Thanks for the details. And it seems that UniqueConstraint, which can be dependent on other data, can’t be configured to check uniqueness “both ways”.

7

Correct. Since this is a symmetrical relationship, you’d have to do something like implement the requirement that node_a_id must always be less than node_b_id to ensure you can enforce bidirectional uniqueness.

1 Like
8

Thinking way outside the box, you could also create a “synthetic key” column doing something like “(%d,%d)” % (min(a, b), max(a,b)) and check that for uniqueness.

2 Likes