-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 '-00-00 00:00:00',
`update_user_id` int(10) NOT NULL default '0',
`add_date` date NOT NULL default '-00-00',
`pricelist_start_date` date NOT NULL default '-00-00',
`pricelist_end_date` date NOT NULL default '-12-31',
`sales_end_date` date NOT NULL default '-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
