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 calledmy_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 thename
column. This means that no two rows in the table can have the same value in thename
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.