---
title: "Discussion on \"Why SQL UNIQUE Constraints Allow Duplicate NULLs\" | Hashnode"
description: "Discussion on \"Why SQL UNIQUE Constraints Allow Duplicate NULLs\". Understand why SQL unique constraints permit duplicate NULLs, the impact on composite keys, and how to use NULLS NOT DISTINCT in PostgreSQL."
url: "https://hashnode.com/posts/why-sql-unique-constraints-allow-duplicate-nulls/6af34e6af60522234a475d0f"
---

[Feed](https://hashnode.com/feed) /

Discussion

# [Why SQL UNIQUE Constraints Allow Duplicate NULLs](https://doogal.dev/why-sql-unique-constraints-allow-duplicate-nulls?utm_source=hashnode&utm_medium=feed)

Understand why SQL unique constraints permit duplicate NULLs, the impact on composite keys, and how to use NULLS NOT DISTINCT in PostgreSQL.

4h ago · 5 min read · doogal.dev  
Read the article

-   [#sql](https://hashnode.com/tag/sql)
-   [#postgresql](https://hashnode.com/tag/postgresql)
-   [#databasedesign](https://hashnode.com/tag/databasedesign)
-   [#softwareengineering](https://hashnode.com/tag/softwareengineering)

## Written by

[Doogal Simpson](https://hashnode.com/@doogal)

@doogal

  
[View profile](https://hashnode.com/@doogal)

"hey doogal, what is .... ?"

## Responses 2

Markdown supported · ⌘↵ to post

[OneLifeVibe](https://hashnode.com/@onelifevibe)

[Just now](https://hashnode.com/posts/why-sql-unique-constraints-allow-duplicate-nulls/6af34e6af60522234a475d0f/comment/6af392929f34908e6192623f)

The composite-key example is the one that will save someone a real incident. The single-column NULL case is a curiosity, but an (org\_id, NULL) permission row silently duplicating is a genuine integrity hole, and it is exactly the kind of bug that passes every test because the tests only ever insert one org-wide row. I also appreciated the SQL Server contrast — it is easy to assume all engines treat NULL the same way until a migration crosses databases. The partial-index recipe for Postgres 14 and earlier is a useful thing to have written down before you need it.

[SMTasker](https://hashnode.com/@smtasker)

[1h ago](https://hashnode.com/posts/why-sql-unique-constraints-allow-duplicate-nulls/6af34e6af60522234a475d0f/comment/6af37b63056876d44c48f334)

The migration case deserves a fixture too: start with two existing (42, NULL) rows, rather than testing only new inserts after the constraint exists. Group by the intended key and inspect groups with count(\*) > 1 before changing the uniqueness policy. Then decide how to merge or reject those historical duplicates; the new constraint cannot choose the surviving permission record for you.