---
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: "Revati S Misra"  
published: 2023-07-11  
updated: 2023-07-12  
canonical: https://www.mindstick.com/forum/159036/how-can-i-create-a-unique-constraint-that-also-allows-nulls  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 3 minutes  

---

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

How can I create a [unique](https://www.mindstick.com/blog/12870/how-to-be-unique-in-business) [constraint that also allows](https://answers.mindstick.com/qa/100247/how-can-i-create-a-unique-constraint-that-also-allows-nulls) nulls?

## Replies

### Reply by Aryan Kumar

To create a unique [constraint](https://www.mindstick.com/articles/434/constraint-in-sql-server) that also allows nulls, you can use the following syntax:

SQL

```plaintext
CREATE TABLE mytable (
  id INT NOT NULL,
  name VARCHAR(255) NULL,
  CONSTRAINT uc_name UNIQUE (name) WHERE name IS NOT NULL
);
```

This will create a unique constraint on the `name` column, but will allow null values. If you try to insert a row with a non-null value for `name` that is already in the table, the insert will fail. However, you can insert multiple rows with a null value for `name`.

Here is an explanation of the syntax:

- `CREATE TABLE` creates a new table.
- `mytable` is the name of the new table.
- `id` is an integer column that is not null.
- `name` is a varchar column that can be null.
- `CONSTRAINT uc_name` is the name of the unique constraint.
- `UNIQUE` specifies that the values in the `name` column must be unique.
- `(name)` specifies the column that the unique constraint applies to.
- `WHERE name IS NOT NULL` specifies that the unique constraint only applies to rows where the `name` column is not null.

### Reply by Tehran Noorani

Sure, here's how you can create a [unique constraint](https://www.mindstick.com/forum/158047/sql-server-unique-constraint-with-duplicate-nulls) 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 is allowed to contain null values. However, the `unique_name` constraint will still prevent duplicate rows from being inserted into the table, even if one or both of the `name` values are null.

Here's an 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 column that can be null.
- The `CONSTRAINT unique_name UNIQUE (name)` clause 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 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 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://www.mindstick.com/forum/159036/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.