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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
