---
title: "How can I create a unique constraint that also allows nulls?"  
description: "How can I create a unique constraint that also allows nulls?"  
author: "Rocky Dada"  
published: 2023-07-13  
updated: 2023-07-13  
canonical: https://answers.mindstick.com/qa/100247/how-can-i-create-a-unique-constraint-that-also-allows-nulls  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# How can I create a unique constraint that also allows nulls?



## Answers

### Answer by Rocky Dada

Sure, here's how you can create a [unique constraint](https://www.mindstick.com/interview/90/what-is-a-constraint) that also allows null:

SQL

```plaintext
CREATE TABLE my_table (
  id INT NOT NULL,
  name VARCHAR(255) NULL,
  CONSTRAINT unique_name UNIQUE (name)
);
```

In this example, the `name` [column](https://www.mindstick.com/forum/2288/column-site_name-ambiguous-problem) is allowed to contain [null values](https://www.mindstick.com/forum/159818/how-to-use-the-coalesce-function-to-handle-null-values-in-sql-queries). However, the `unique_name` constraint will still prevent [duplicate rows](https://www.mindstick.com/forum/157765/how-to-remove-duplicate-rows-in-sql) from being inserted into the [table](https://www.mindstick.com/articles/43918/how-to-design-table-using-bootstrap), even if one or both of the `name` values are null.

Here's an [explanation](https://yourviews.mindstick.com/story/4966/why-the-peepal-tree-is-worshipped-the-scientific-explanation) of the code:

- The `CREATE TABLE` statement creates a new table called `my_table`.
- The `id` column is an integer column that cannot be null.
- The `name` column is a [string](https://www.mindstick.com/articles/1717/string-string-builder-and-string-buffer-in-java) column that can be null.
- The `CONSTRAINT unique_name UNIQUE (name)` [clause](https://www.mindstick.com/interview/94/why-can-a-group-by-or-order-by-clause-be-expensive-to-process) creates a unique constraint on the `name` column. This means that no two rows in the table can have the same value in the `name` column, even if one or both of the values are null.

To [insert](https://www.mindstick.com/articles/81/insert-update-delete-records-in-csharp-dot-net) a row into the table, you would use the following syntax:

SQL

```plaintext
INSERT INTO my_table (id, name) VALUES (1, 'John Doe');
```

You could also insert a row with a [null value](https://www.mindstick.com/forum/23045/document-body-appendchild-has-a-null-value) in the `name` column:

SQL

```plaintext
INSERT INTO my_table (id, name) VALUES (2, NULL);
```

In either case, the `unique_name` constraint would prevent you from inserting a second row with the same value in the `name` column, even if the value is null.


---

Original Source: https://answers.mindstick.com/qa/100247/how-can-i-create-a-unique-constraint-that-also-allows-nulls

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.