Consolidate uniqueness constraints which reference nullable fields #1095

New Issue

MrUnknownDE · 2026-04-05T20:34:47+02:00

MrUnknownDE commented

2026-04-05 20:34:47 +02:00

Originally created by @jeremystretch on 10/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 10/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.

Sign in to join this conversation.

Branches Tags

main

21455-sql-indexes-audit

21357-register-model-actions

feature

20924-plugin-ui-components

21766-improve-test-coverage-for-sortable-table-columns

21157-export-template-public-models

21025-pre-render-config-contexts

21364-swagger

feature-ip-prefix-link

20911-dropdown-3

fix_module_substitution

21160-filterset

20911-dropdown-2

20044-elevation-stuck-lightmode

7604-filter-modifiers-v3

20660-script-load

19724-graphql

14884-script

19720-macaddress-interface-generic-relation

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

No Label netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox status: blocked status: blocked status: blocked status: blocked status: blocked status: blocked status: blocked type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping type: housekeeping

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github/netbox#1095