Menu
Let's say I have a table where I want the values of MY_COLUMN to be unique except when the value is 'xyz'. In other words, MY_COLUMN may be 'xyz' in multiple rows, but all values that are not 'xyz' must be unique. Is it possible to set a constraint that achieves this?
249
The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
The following constraints are commonly used in SQL: NOT NULL - Ensures that a column cannot have a NULL value. UNIQUE - Ensures that all values in a column are different. PRIMARY KEY - A combination of a NOT NULL and UNIQUE .
You can insert NULL values into columns with the UNIQUE constraint because NULL is the absence of a value, so it is never equal to other NULL values and not considered a duplicate value. This means that it's possible to insert rows that appear to be duplicates if one of the values is NULL .
When we will add a UNIQUE constraint on the same column multiple times then MySQL will create the index on that column for a number of times we have added the UNIQUE constraint.
According to the documentation
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.
So create a nullable column and when you're querying your table just use ISNULL(MY_COLUMN, 'xyz').
74
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
