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

Question

Aryan Kumar · Answer

To create a unique constraint that also allows nulls, you can use the following syntax:

SQL

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.

Tehran Noorani · Answer

Sure, here's how you can create a unique constraint that also allows null:

SQL

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

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

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.

forum

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

Can you answer this question?

2 Answers