1

I'm trying to define a unique constraint in MySQL that will treat NULLs as non-distinct to each other.

I.e.
If I have a table like this:

column1 int(11) NOT NULL,
column2 int(11) DEFAULT NULL

whose values are populated by AUTO INCREMENT INT, and then define a unique constraint as follows:

UNIQUE KEY uniq1 (column1, column2),

I can insert (1, NULL) multiple times because of MySQLs behaviour to treat NULLs as distinct to each other in a unique constraint. If however I actually wanted to prevent such duplicate insertion, is there a way other than assigning a magic value?

Enno Shioji
  • 26,542
  • 13
  • 70
  • 109

1 Answers1

3

As documented under CREATE INDEX Syntax:

For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

You cannot define an index over an expression (such as given in your question), therefore MySQL's UNIQUE indexes cannot enforce the constraint as you wish.

Instead, you can create a BEFORE INSERT trigger that raises an error if a matching record already exists:

DELIMITER ;;

CREATE TRIGGER uniq1 BEFORE INSERT ON my_table FOR EACH ROW
  IF EXISTS(
    SELECT *
    FROM   my_table
    WHERE  column1 <=> NEW.column1 AND column2 <=> NEW.column2
    LIMIT  1
  ) THEN
    SIGNAL
      SQLSTATE '23000'
      SET MESSAGE_TEXT = 'Duplicate entry for key uniq1';
  END IF;;

To prevent UPDATEs from causing a similar problem, you will probably want to create a similar BEFORE UPDATE trigger too.

eggyal
  • 122,705
  • 18
  • 212
  • 237