List:General Discussion« Previous MessageNext Message »
From:Lev Lvovsky Date:April 17 2009 12:29am
Subject:InnoDB best practices for ensuring unique tuple where one column can be NULL
View as plain text  
hello,

assume the following table:

CREATE TABLE t (
id INT UNSIGNED auto_increment PRIMARY KEY,
c1 INT UNSIGNED NOT NULL,
c2 INT UNSIGNED NOT NULL,
c3 INT UNSIGNED,
UNIQUE (c1, c2, c3)
) engine = InnoDB;

Our first issue is that the UNIQUE constraint on (c1,c2,c3) does not  
work in the case that a NULL value for c3 is inserted:
mysql> insert into t (c1, c2, c3) VALUES (1,1,NULL);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t (c1, c2, c3) VALUES (1,1,NULL);
Query OK, 1 row affected (0.01 sec)

Given this behavior, we cannot rely on the UNIQUE constraint to  
enforce two sets of otherwise identical values. However, in addition  
to the UNIQUE requirement that we have
above, we *only* want the UNIQUE constraint to be checked when the c3  
column has a NULL value, e.g.:

--
insert of (1,1,NULL) and (1,1,NULL): error
insert of (1,1,1) and (1,1,1): ok
--

Clearly the latter case would not be allowed with a UNIQUE(c1,c2,c3)  
constraint.

Attempting to ensure these constraints via triggers is problematic,  
because within separate transactions two different clients can insert  
identical values, and once finished
with the transaction, the triggers will already have done their  
validation finding no error.

Is there a standard way to perform this sort of checking?

thanks!
-lev
Thread
InnoDB best practices for ensuring unique tuple where one column can be NULLLev Lvovsky17 Apr