Set Unique Constraint only under the direct parents (Django MPTT)

Question

I created "Category" model with Django MPTT:

from django.db import models
from mptt.models import MPTTModel, TreeForeignKey

class Category(MPTTModel):
    name = models.CharField(max_length=50)
    parent = TreeForeignKey(
        "self", 
        on_delete=models.CASCADE, 
        null=True, 
        blank=True, 
        related_name="children"
    )

But, with this "Category" model, I could add the duplicated data "3F" and "4F" under "Building B"(Direct Parent) as shown below:

• USA
  • New York
    • Building A
      • 3F
      • 4F
    • Building B
      • 3F // Here
      • 3F // Here
      • 4F // Here
      • 4F // Here

So I added "unique=True" to "name" field in "Category" model:

from django.db import models
from mptt.models import MPTTModel, TreeForeignKey

class Category(MPTTModel):                 // Here
    name = models.CharField(max_length=50, unique=True)
    parent = TreeForeignKey(
        "self", 
        on_delete=models.CASCADE, 
        null=True, 
        blank=True, 
        related_name="children"
    )

But with this "Category" model, I couldn't add the data "3F" and "4F" under "Building B" anymore because "3F" and "4F" already exist under "Building A" in "Category". I found adding "unique=True" to "name" field in "Category" model sets Unique Constraint in whole "Category" model about "name" field. So if there are the same "name" values such as "F3" and "F4" anywhere in "Category", we cannot add the same "name" values such as "F3" and "F4" anywhere in "Category". In short, if "3F" and "4F" already exist anywhere in "Category", we cannot add "3F" and "4F" anywhere in "Category":

• USA
  • New York
    • Building A
      • 3F // Because already exists
      • 4F // Because already exists
    • Building B
      • 3F // So cannot add
      • 3F // So cannot add
      • 4F // So cannot add
      • 4F // So cannot add

This is my desired result not allowing duplicated data only under the direct parents:

• USA
  • New York
    • Building A
      • 3F // Even though already exists
      • 4F // Even though already exists
    • Building B
      • 3F // 〇 But allowed to add
      • 3F // ✖ Not allowed to add
      • 4F // 〇 But allowed to add
      • 4F // ✖ Not allowed to add

Are there any ways to set Unique Constraint only under the direct parents?

Answer 1

Remove "unique=True" from "name" field:

name = models.CharField(max_length=50) # "unique=True" is removed

Then, add this code below to "Category" model :

class Meta:
    unique_together = [['name', 'parent']]

This is the full code:

from django.db import models
from mptt.models import MPTTModel, TreeForeignKey

class Category(MPTTModel): # "unique=True" is removed
    name = models.CharField(max_length=50)
    parent = TreeForeignKey(
        "self", 
        on_delete=models.CASCADE, 
        null=True, 
        blank=True, 
        related_name="children"
    )

    class Meta:
        unique_together = [['name', 'parent']]

This code above realizes what you desire:

• USA
  • New York
    • Building A
      • 3F // Even though already exists
      • 4F // Even though already exists
    • Building B
      • 3F // 〇 But allowed to add
      • 3F // ✖ Not allowed to add
      • 4F // 〇 But allowed to add
      • 4F // ✖ Not allowed to add

But only the top level(level 1) still doesn't have Unique Constraint so the top level(level 1) still can have duplicated data as shown below:

• USA
  • New York
    • Building A
      • 3F
      • 4F
    • Building B
      • 3F
      • 4F
• USA // 〇 Allowed to add
• USA // 〇 Allowed to add.

Actually, I tried some solutions to add Unique Constraint to the top level(level 1) but I couldn't so I recommend not to use the top level(level 1) to store the proper data which you actually use for your app. So instead, to the top level(level 1), just add "Root", "Top" or whatever shows "This is the top level(level 1)" as shown below:

• Root // Here
  • USA
    • New York
      • Building A
        • 3F
        • 4F
      • Building B
        • 3F
        • 4F