How exactly works the MySql unique constraint defined on a nullable field?

Question

I am not so into database (I am using MySql) and I have the following doubt about the **unique constraint setted on a nullable field

So from what I know the UNIQUE constraint ensures that all values in a column are different.

But what happens if I set a unique constraint on a nullable field of a table.

For example I have this DDL table definition:

CREATE TABLE results (
  id        BigInt UNSIGNED NOT NULL AUTO_INCREMENT,
  sample_id VarChar(128) NOT NULL,
  doi       VarChar(128),
  result    VarChar(2) NOT NULL,
  error     Text, 
  PRIMARY KEY (
      id
  )
) ;
ALTER TABLE results COMMENT = '';
ALTER TABLE results ADD CONSTRAINT fk_results_pgrfas FOREIGN KEY (sample_id)
  REFERENCES pgrfas (sample_id)
  ON DELETE NO ACTION 
  ON UPDATE NO ACTION;
ALTER TABLE results ADD CONSTRAINT u_results UNIQUE 
    (doi);

where the doi field is nullable. On this field is setted the unique constraint because I want prevent that more rows of this table have the same values of the doi field.

Each rows needs to have a different doi values but this field can be null so I need to have that multiple rows have the doi field setted to null.

The unique constraint works only on setted values or also prevent that differents rows have null value for this doi field? (this will be a problem for me because I need that the unique constraint only works on not null values).

So can I insert multiple rows having the doi field setted to null?

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-03-17T11:09:38

The answer is buried deep in the documentation for create index:

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.

Note that this behavior varies among databases. Some allow only one NULL value.

For your purpose, the unique index/constraint does just what you need.

How exactly works the MySql unique constraint defined on a nullable field?

1 Answers