starred/netbox
1
0
Fork 0
mirror of https://github.com/netbox-community/netbox.git synced 2026-01-11 21:10:29 +01:00
Code Issues 273 Packages Projects Releases 10 Wiki Activity

Extend case-insensitive uniqueness constraints #7931

New Issue
Open
opened 2025-12-29 20:30:10 +01:00 by adam · 10 comments
adam commented 2025-12-29 20:30:10 +01:00
Owner
Copy Link

Originally created by @candlerb on GitHub (Apr 21, 2023).

NetBox version

v3.4.8

Feature type

Change to existing functionality

Proposed functionality

Introduced in v3.4.0: "Device and virtual machine names are no longer case-sensitive"

Should this be extended to other models which have unique constraints on name, in particular Site and Tag?

Use case

Same reason as doing this on devices and VMs: for data validation purposes.

It seems weird to allow site "foo bar" and "Foo bar" as two different sites, when site names are supposedly "unique".

Database changes

I believe enforcement is via a database btree index

External dependencies

None

Originally created by @candlerb on GitHub (Apr 21, 2023). ### NetBox version v3.4.8 ### Feature type Change to existing functionality ### Proposed functionality Introduced in v3.4.0: _"Device and virtual machine names are no longer case-sensitive"_ Should this be extended to other models which have unique constraints on name, in particular Site and Tag? ### Use case Same reason as doing this on devices and VMs: for data validation purposes. It seems weird to allow site "foo bar" and "Foo bar" as two different sites, when site names are supposedly "unique". ### Database changes I believe enforcement is via a database btree index ### External dependencies None
adam added the type: featurecomplexity: mediumnetboxstatus: backlog labels 2025-12-29 20:30:10 +01:00
adam commented 2025-12-29 20:30:10 +01:00
Author
Owner
Copy Link

@github-actions[bot] commented on GitHub (Aug 4, 2023):

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. Do not attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our contributing guide.

@github-actions[bot] commented on GitHub (Aug 4, 2023): This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. **Do not** attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our [contributing guide](https://github.com/netbox-community/netbox/blob/develop/CONTRIBUTING.md).
adam commented 2025-12-29 20:30:10 +01:00
Author
Owner
Copy Link

@github-actions[bot] commented on GitHub (Oct 15, 2024):

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. Do not attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our contributing guide.

@github-actions[bot] commented on GitHub (Oct 15, 2024): This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. **Do not** attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our [contributing guide](https://github.com/netbox-community/netbox/blob/develop/CONTRIBUTING.md).
adam commented 2025-12-29 20:30:10 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Oct 15, 2024):

Whoops, I removed the status tag by mistake.

@jeremystretch commented on GitHub (Oct 15, 2024): Whoops, I removed the status tag by mistake.
adam commented 2025-12-29 20:30:10 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Jun 20, 2025):

I think we can tackle this now by introducing a case-insensitive PostgreSQL collation:

from django.contrib.postgres.operations import CreateCollation
from django.db import migrations

class Migration(migrations.Migration):
    operations = [
        CreateCollation(
            "case_insensitive",
            provider="icu",
            locale="und-u-ks-level2",
            deterministic=False,
        ),
    ]

And employing it on the relevant fields:

class Site(models.Model):
    name = models.CharField(
        unique=True,
        db_collation='case_insensitive',
        ...
    )
@jeremystretch commented on GitHub (Jun 20, 2025): I think we can tackle this now by introducing a case-insensitive PostgreSQL collation: ```python from django.contrib.postgres.operations import CreateCollation from django.db import migrations class Migration(migrations.Migration): operations = [ CreateCollation( "case_insensitive", provider="icu", locale="und-u-ks-level2", deterministic=False, ), ] ``` And employing it on the relevant fields: ```python class Site(models.Model): name = models.CharField( unique=True, db_collation='case_insensitive', ... ) ````
adam commented 2025-12-29 20:30:11 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Jul 17, 2025):

Bumping this to v4.5 as it is potentially a breaking change. For example, if a NetBox installation has two sites named "A" and "a", this change would prevent modification of either site until one of them is renamed to satisfy the stricter uniqueness constraint.

@jeremystretch commented on GitHub (Jul 17, 2025): Bumping this to v4.5 as it is potentially a breaking change. For example, if a NetBox installation has two sites named "A" and "a", this change would prevent modification of either site until one of them is renamed to satisfy the stricter uniqueness constraint.
adam commented 2025-12-29 20:30:11 +01:00
Author
Owner
Copy Link

@sleepinggenius2 commented on GitHub (Jul 17, 2025):

The Site model—as an example—already has db_collation="natural_sort" on it. I think you would need this additional collation to support that:

CreateCollation(
    "natural_sort_case_insensitive",
    provider="icu",
    locale="und-u-kn-true-ks-level2",
    deterministic=False,
)
@sleepinggenius2 commented on GitHub (Jul 17, 2025): The `Site` model—as an example—already has `db_collation="natural_sort"` on it. I think you would need this additional collation to support that: ```python CreateCollation( "natural_sort_case_insensitive", provider="icu", locale="und-u-kn-true-ks-level2", deterministic=False, ) ```
adam commented 2025-12-29 20:30:11 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Oct 23, 2025):

Unfortunately I've encountered an issue with this approach. Django creates a varchar_pattern_ops index on all CharFields with unique=True if no collation is defined:

    "circuits_provider_name_8f2514f5_like" btree (name varchar_pattern_ops)
    "circuits_provider_name_key" UNIQUE CONSTRAINT, btree (name)

This is the case for most (all?) of our existing unique CharFields, and prevents us from adding a non-deterministic collation to the column, i.e. db_collation='case_insensitive' (see Django bugs #33901 and #34898):

django.db.utils.NotSupportedError: nondeterministic collations are not supported for operator class "varchar_pattern_ops"

(Apparently, PostgreSQL 18 supports LIKE indexes on columns with non-deterministic collations, but it will be years before we can enforce that as a minimum version for NetBox.)

Our two options at this stage appear to be

  1. Manually drop all of the varchar_pattern_ops indexes. Feels dangerous, but Django v4.2+ simply doesn't create them anymore anyway.
  2. Instead of using a collation, define/alter UniqueConstraints referencing e.g. Lower('name'). (This is what we did for device & VM names under #9249.)

Need to spend some more time with this.

@jeremystretch commented on GitHub (Oct 23, 2025): Unfortunately I've encountered an issue with this approach. Django creates a `varchar_pattern_ops` index on all CharFields with `unique=True` if no collation is defined: ``` "circuits_provider_name_8f2514f5_like" btree (name varchar_pattern_ops) "circuits_provider_name_key" UNIQUE CONSTRAINT, btree (name) ``` This is the case for most (all?) of our existing unique CharFields, and prevents us from adding a non-deterministic collation to the column, i.e. `db_collation='case_insensitive'` (see Django bugs [#33901](https://code.djangoproject.com/ticket/33901) and [#34898](https://code.djangoproject.com/ticket/34898)): ``` django.db.utils.NotSupportedError: nondeterministic collations are not supported for operator class "varchar_pattern_ops" ``` (Apparently, PostgreSQL 18 supports `LIKE` indexes on columns with non-deterministic collations, but it will be years before we can enforce that as a minimum version for NetBox.) Our two options at this stage appear to be 1. Manually drop all of the `varchar_pattern_ops` indexes. Feels dangerous, but Django v4.2+ simply doesn't create them anymore anyway. 2. Instead of using a collation, define/alter UniqueConstraints referencing e.g. `Lower('name')`. (This is what we did for device & VM names under #9249.) Need to spend some more time with this.
adam commented 2025-12-29 20:30:11 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Oct 24, 2025):

I've opted to go with option 1 above (dropping the varchar_pattern_ops indexes) as it seems to more reliably handle Unicode values and can be implemented more reliably.

@jeremystretch commented on GitHub (Oct 24, 2025): I've opted to go with option 1 above (dropping the `varchar_pattern_ops` indexes) as it seems to more reliably handle Unicode values and can be implemented more reliably.
adam commented 2025-12-29 20:30:12 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Oct 27, 2025):

Unfortunately, the use of a non-deterministic collation breaks all LIKE lookups for the field (not just indexes). This precludes us from pursuing this approach, as it would be very breaking (e.g. any foo_icontains='bar' queries raise an exception).

I'm going to start over using Lower() on UniqueConstraints for the relevant fields.

@jeremystretch commented on GitHub (Oct 27, 2025): Unfortunately, the use of a non-deterministic collation breaks all `LIKE` lookups for the field (not just indexes). This precludes us from pursuing this approach, as it would be very breaking (e.g. any `foo_icontains='bar'` queries raise an exception). I'm going to start over using `Lower()` on UniqueConstraints for the relevant fields.
adam commented 2025-12-29 20:30:12 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Oct 31, 2025):

After some discussion with the other maintainers, we've decided to bump this from v4.5 as we've exceeded its time budget, and we don't want to risk the other v4.5 initiatives slipping. At least now we've determined a clear path forward, to be revisited in a future release.

@jeremystretch commented on GitHub (Oct 31, 2025): After some discussion with the other maintainers, we've decided to bump this from v4.5 as we've exceeded its time budget, and we don't want to risk the other v4.5 initiatives slipping. At least now we've determined a clear path forward, to be revisited in a future release.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
beta
breaking change
complexity: high
complexity: low
complexity: medium
needs milestone
netbox
pending closure
plugin candidate
pull-request
Mirrored from GitHub Pull Request
 severity: high
severity: low
severity: medium
status: accepted
status: backlog
status: blocked
status: duplicate
status: needs owner
status: needs triage
status: revisions needed
status: under review
topic: GraphQL
topic: Internationalization
topic: OpenAPI
topic: UI/UX
topic: cabling
topic: event rules
topic: htmx navigation
topic: industrialization
topic: migrations
topic: plugins
topic: scripts
topic: templating
topic: testing
type: bug
type: deprecation
type: documentation
type: feature
type: housekeeping
type: translation
No Label complexity: medium netbox status: backlog type: feature
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/netbox#7931
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?