> From: Dan Buettner [mailto:[EMAIL PROTECTED]
> This is a feature - a NULL value is an undefined value,
> therefore two NULL values are not the same. Can be a little
> confusing but makes sense when you think about it.
>
> A UNIQUE index does ensure that non-NULL values are unique;
> you could specify that your column not accept NULL values.
I was afraid to hear something like this. I'd rather not use some invalid
value to mark unknown fields. So I went a bit further, and tried to ensure
the uniqueness of the null values with a trigger.
CREATE TRIGGER Target_Before_Insert BEFORE INSERT ON Target
FOR EACH ROW IF NEW.IMSI IS NULL OR NEW.IMEI IS NULL THEN
BEGIN
DECLARE c_ INT UNSIGNED;
SELECT COUNT(*) INTO c_ FROM Target
WHERE IMSI <=> NEW.IMSI AND IMEI <=> NEW.IMEI;
IF c_ THEN SET NEW.Id = NULL;
END IF;
END;
END IF;
Here Id is a non NULL field, so setting it to NULL should trigger an error.
But when I run an insert where the trigger body would run, then I get the
following error.
Table 'Target' was not locked with LOCK TABLES
I am pretty sure, I don't use LOCK TABLES at all. What is going on here?
How can I get rid of this error?
Thx
ImRe
>
> Dan
>
>
> On 12/11/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > I have an InnoDB table similar to this:
> >
> > CREATE TABLE Target
> > (IMSI VARCHAR(15) ASCII,
> > IMEI VARCHAR(15) ASCII,
> > UNIQUE KEY (IMSI, IMEI));
> >
> > After playing a bit with it, I managed to add duplicate records, if
> > one of the fields was a NULL:
> >
> > +-----------------+-----------------+
> > | IMSI | IMEI |
> > +-----------------+-----------------+
> > | NULL | 35195600126418 |
> > | NULL | 35195600126418 |
> > +-----------------+-----------------+
> >
> >
> > Is this a bug, or a feature? :-)
> >
> > If it is a feature, than how can I assure uniqueness for a
> table in a
> > sense that won't allow such duplicates?
> >
> > Thx
> >
> > ImRe
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
