> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Harald Fuchs > Sent: Tuesday, February 25, 2003 10:52 AM > To: [EMAIL PROTECTED] > Subject: Re: Problem with unique index on InnoDB >
> The second record is different because it has another model_id. > > BTW: your index on model_id is useless because it's covered > by your primary key. > Yes, correct... Primary key is based on model_id, and model_id field is set as autoincrement. But there is also unique index called "model", and there is no model_id field inside... So mysql should not add two same records... > > In article <[EMAIL PROTECTED]>, > "Marek Lewczuk" <[EMAIL PROTECTED]> writes: > > > Hello group, > > My table look like this: > > > CREATE TABLE `mda_models` ( > > `model_id` int(10) NOT NULL auto_increment, > > `make_id` int(10) NOT NULL default '0', > > `model_name` varchar(100) NOT NULL default '', > > `model_phase` varchar(100) default NULL, > > `model_phase_no` int(4) default NULL, > > `model_manufacturer_name` text, > > `model_type` int(10) NOT NULL default '0', > > `model_wage` int(4) default NULL, > > `update_date` datetime NOT NULL default '0000-00-00 00:00:00', > > `update_user_id` int(10) NOT NULL default '0', > > `add_date` date NOT NULL default '0000-00-00', > > `pricelist_start_date` date NOT NULL default '0000-00-00', > > `pricelist_end_date` date NOT NULL default '9999-12-31', > > `sales_end_date` date NOT NULL default '9999-12-31', > > PRIMARY KEY (`model_id`,`make_id`), > > UNIQUE KEY `model` > > > (`model_name`,`model_phase`,`model_phase_no`,`model_type`,`make_id`), > > KEY `make_id` (`make_id`), > > KEY `model_id` (`model_id`), > > KEY `model_type` (`model_type`), > > FOREIGN KEY (`make_id`) REFERENCES `mda_makes` (`make_id`), > > FOREIGN KEY (`model_type`) REFERENCES `set_mda_car_types` > > (`type_id`) > > ) TYPE=InnoDB > > > As you see there is the unique key called "model". There > are several > > fields, some of them can be null.. Look at this insert querys: > > > INSERT INTO mda_models (make_id, model_name, model_phase, > > model_phase_no, model_type) VALUES (42, 'Test model', NULL, > NULL, 1); > > INSERT INTO mda_models (make_id, model_name, model_phase, > > model_phase_no, model_type) VALUES (42, 'Test model', NULL, > NULL, 1); > > > MySQL should send an error before second query, that there is the > > record which is the same as the inserting one... But NO, > you can add > > as many as you want... Is this a bug or I'm doing something wrong. > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
