[RESOLVED] PHP + MySQL - #1062 - Duplicate entry '' for key 2

bandit

I am utterly stumped here. I have a table called members;

CREATE TABLE `member` (
  `id` int(6) NOT NULL auto_increment,
  `user_id` int(6) NOT NULL default '0',
  `member_id` varchar(6) default NULL,
  `venue_id` int(6) NOT NULL default '0',
  `points` int(7) NOT NULL default '0',
  `name_alias` varchar(32) NOT NULL default '',
  `name_first` varchar(64) NOT NULL default '',
  `name_last` varchar(64) NOT NULL default '',
  `sex` char(1) NOT NULL default '',
  `dob` date NOT NULL default '0000-00-00',
  `phone` varchar(32) NOT NULL default '',
  `address` text NOT NULL,
  `location` varchar(64) NOT NULL default '',
  `signature` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `nickname` (`name_alias`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And up till yesterday it has been working fine - there are 9 entries in it. But now, whenever I attempt to add something;

INSERT INTO `member` (`id`,`user_id`,`venue_id`) VALUES ("","9","2");

Only to receive;

#1062 - Duplicate entry '' for key 2

Now the odd thing is that if I change my insert query to;

INSERT INTO `member` (`user_id`,`venue_id`) VALUES ("9","2");

I still receive the EXACT same error. MySQL bug maybe?

I hope one of you experts can help me 🙁 :bemused:

NogDog

I suspect it's because you have a unique key on the name_alias field, and since you are not setting a value for it in your insert statements, the first time you run it you have a record with a default value of "" for name_alias, so it won't let you enter any more.

bandit

Wow, you're 100% correct. Can't believe I didn't think of that - the really odd error MySQL was giving me was totally pointing me in the wrong direction.

Thanks!

NogDog

Actually, in its own terse manner, it was telling you: Duplicate entry '' for key 2, where "key 2" is the 2nd key (a.k.a. "index") for that table. 🙂

bandit

Ah, of course - my brain was purely thinking about the error from the perspective of my INSERT query 🙁

laserlight

Please mark this thread as resolved (if it is).

bandit

I tried to, but all it did was edit my post's title ?_?

NogDog

You mark it via the "Thread Tools" drop-down at the top of the thread. I've got this one....