pgSQL vs T-SQL – Enforce uniqueness on a column but allow multiple nulls

pgSQL vs T-SQL – Enforce uniqueness on a column but allow multiple nulls

A Unique constraint is used to enforce unique values across one or many columns, often applied against Alternate keys of a table. Sometimes you want uniqueness enforced only where data for a column is present, and not where values are Null, for example, when storing optional Person attributes such as National Insurance numbers or Driving Licence numbers. Section 5.3.3. Unique Constraints of theĀ  Postgres manual states:

“In general, a unique constraint is violated when there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.”

 

SQL Server is one such database that doesn’t follow the SQL standard here. For Unique Constraints, it treats a Null like any other value rather than a real unknown. The work around in T-SQL is to use a Filtered Unique Index – one with a WHERE clause.

 

Example

pgSQL T-SQL

Leave a Reply

Your email address will not be published. Required fields are marked *