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?

Revati S Misra

Can you answer this question?

2 Answers

Liked By