List:General Discussion« Previous MessageNext Message »
From:<imre Date:December 12 2006 6:42am
Subject:RE: UNIQUE KEY vs NULLs
View as plain text  
> From: Dan Buettner [mailto:drbuettner@stripped] 
> 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, imre@stripped <imre@stripped> 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/mysql?unsub=1
> >
> >
> 
> 
> 


Thread
UNIQUE KEY vs NULLsimre11 Dec
RE: UNIQUE KEY vs NULLsemierzwa11 Dec
Re: UNIQUE KEY vs NULLsDan Buettner11 Dec
  • RE: UNIQUE KEY vs NULLsimre12 Dec
Re: UNIQUE KEY vs NULLsMartijn Tonies11 Dec