Consolidate uniqueness constraints which reference nullable fields #11707

New Issue

adam · 2025-12-29T21:48:52+01:00

adam commented

2025-12-29 21:48:52 +01:00

Originally created by @jeremystretch on GitHub (Oct 9, 2025).

Proposed Changes

There are several instances in NetBox where we define two UniqueConstraints to handle nullable fields: One to handle null values, and one to handle non-null values. The Region model is one example. The UniqueConstraints defined below ensure that the name field is unique to its parent, if one is assigned, or otherwise unique to regions having no parent:

class Meta:
    constraints = (
        # Has no effect if parent is NULL, because PostgreSQL considers NULL != NULL
        models.UniqueConstraint(
            fields=('parent', 'name'),
            name='%(app_label)s_%(class)s_parent_name'
        ),
        # Checks that name is unique if parent is NULL
        models.UniqueConstraint(
            fields=('name',),
            name='%(app_label)s_%(class)s_name',
            condition=Q(parent__isnull=True),
            violation_error_message=_("A top-level region with this name already exists.")
        ),
    ...
    )

PostgreSQL 15 introduced support for declaring NULLS NOT DISTINCT on unique indexes, allowing for the two above constraints to be condensed into one by setting nulls_distinct=False:

class Meta:
    constraints = (
        models.UniqueConstraint(
            fields=('parent', 'name'),
            name='%(app_label)s_%(class)s_parent_name',
            nulls_distinct=False,  # Assert that NULL == NULL
        ),
    ...
    )

NOTE: This is not supported on PostgreSQL versions earlier than 15.

Justification

This will reduce the overall number of unique constraints we have defined, and greatly simplify the logic for ensuring uniqueness where nullable fields must be considered.

Originally created by @jeremystretch on GitHub (Oct 9, 2025). ### Proposed Changes There are several instances in NetBox where we define two UniqueConstraints to handle nullable fields: One to handle null values, and one to handle non-null values. The Region model is one example. The UniqueConstraints defined below ensure that the `name` field is unique to its parent, if one is assigned, or otherwise unique to regions having _no_ parent: ```python class Meta: constraints = ( # Has no effect if parent is NULL, because PostgreSQL considers NULL != NULL models.UniqueConstraint( fields=('parent', 'name'), name='%(app_label)s_%(class)s_parent_name' ), # Checks that name is unique if parent is NULL models.UniqueConstraint( fields=('name',), name='%(app_label)s_%(class)s_name', condition=Q(parent__isnull=True), violation_error_message=_("A top-level region with this name already exists.") ), ... ) ``` [PostgreSQL 15](https://www.postgresql.org/docs/release/15.0/) introduced support for declaring `NULLS NOT DISTINCT` on unique indexes, allowing for the two above constraints to be condensed into one by setting `nulls_distinct=False`: ```python class Meta: constraints = ( models.UniqueConstraint( fields=('parent', 'name'), name='%(app_label)s_%(class)s_parent_name', nulls_distinct=False, # Assert that NULL == NULL ), ... ) ``` **NOTE:** This is not supported on PostgreSQL versions earlier than 15. ### Justification This will reduce the overall number of unique constraints we have defined, and greatly simplify the logic for ensuring uniqueness where nullable fields must be considered.

adam added the type: housekeeping status: blocked netbox labels 2025-12-29 21:48:52 +01:00

Sign in to join this conversation.

Branches Tags

main

update-changelog-comments-docs

feature-removal-issue-type

20911-dropdown

20239-plugin-menu-classes-mutable-state

21097-graphql-id-lookups

feature

fix_module_substitution

20923-dcim-templates

20044-elevation-stuck-lightmode

feature-ip-prefix-link

v4.5-beta1-release

20068-import-moduletype-attrs

20766-fix-german-translation-code-literals

20378-del-script

7604-filter-modifiers-v3

circuit-swap

12318-case-insensitive-uniqueness

20637-improve-device-q-filter

20660-script-load

19724-graphql

20614-update-ruff

14884-script

02496-max-page

19720-macaddress-interface-generic-relation

19408-circuit-terminations-export-templates

20203-openapi-check

fix-19669-api-image-download

7604-filter-modifiers

19275-fixes-interface-bulk-edit

fix-17794-get_field_value_return_list

11507-show-aggregate-and-rir-on-api

9583-add_column_specific_search_field_to_tables

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/netbox#11707