What does a UNIQUE constraint guarantee, and what does it not?
It says that no two rows can have the same value in that column. That is the whole of it, and there are three things it does not do.
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
email VARCHAR(200) NOT NULL UNIQUE,
display_name VARCHAR(120) NOT NULL
);
So what does it not do? Three things.
It does not ignore capital letters. The values [email protected] and [email protected] are two different values as far as the rule is concerned, so two people can sign up with the same address in two spellings.
It does not ignore spaces. A value with a trailing space is a different value from the same value without it, and that is a bug that surprises people who type an address into a form.
And it does not stop a missing value. In most engines you can have as many rows as you like with a missing value in a unique column, because two missing values are not the same value. That is usually what you want, but it is worth knowing.
So the rule to remember is this. The rule compares the values exactly as they are stored. If you want a looser comparison, then tidy the values before they are stored, and that is a job for the program that writes the rows.
The next two questions in this topic ask for an example and for the rule that only applies to some rows.