Page 1 of 1

UPDATE but SQL said duplicate key

Posted: Sat Jun 23, 2012 9:13 pm
by wvoyance
This is the error message:
=============================================
1062 - Duplicate entry '' for key 'products_model'

update products set products_quantity = '0', products_model = '', products_price = '', products_type = '0', products_date_available = null, products_weight = '0', products_status = '', products_tax_class_id = '', manufacturers_id = '0', products_last_modified = now() where products_id = '541'

[TEP STOP]
================================================
But update any table is surely already exists. What is wrong? What does the error message mean?
Did I set my table wrong?

Re: UPDATE but SQL said duplicate key

Posted: Sun Jun 24, 2012 12:00 am
by requinix
It means there's already a row in the table with products_model="". And this isn't allowed because somewhere you told MySQL that there cannot be duplicates in that column.
Which means that yes, you probably haven't set up the table correctly. What does

Code: Select all

SHOW CREATE TABLE products
output, and can you explain how the table is supposed to be used? (Or point out someplace that you might have already done so.)

Re: UPDATE but SQL said duplicate key

Posted: Sun Jun 24, 2012 12:15 am
by wvoyance
requinix wrote:It means there's already a row in the table with products_model="". And this isn't allowed because somewhere you told MySQL that there cannot be duplicates in that column.
Which means that yes, you probably haven't set up the table correctly. What does

Code: Select all

SHOW CREATE TABLE products
output, and can you explain how the table is supposed to be used? (Or point out someplace that you might have already done so.)

you replied with product_model="" is that because you don't know what should the inside be?
On the other had, the error message had only one quotation mark.
That means the program must be wrong somewhere.....

That is the system program program categories.php for OSC administration for puting/update products.
I did not intentionally changed that part. If there is error must be typo or cause by other modification.

The create table is

====================
drop table if exists `products`;
CREATE TABLE `products` (
`products_id` int(11) NOT NULL AUTO_INCREMENT,
`products_quantity` int(4) NOT NULL,
`products_model` varchar(13) DEFAULT NULL,
`products_image` varchar(64) DEFAULT NULL,
`products_price` decimal(15,4) NOT NULL,
`products_date_added` datetime NOT NULL,
`products_last_modified` datetime DEFAULT NULL,
`products_date_available` datetime DEFAULT NULL,
`products_weight` decimal(5,2) NOT NULL,
`products_status` tinyint(1) NOT NULL,
`products_tax_class_id` int(11) NOT NULL,
`manufacturers_id` int(11) DEFAULT NULL,
`products_ordered` int(11) NOT NULL DEFAULT '0',
`products_type` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`products_id`),
UNIQUE KEY `products_model` (`products_model`),
KEY `idx_products_model` (`products_model`),
KEY `idx_products_date_added` (`products_date_added`)
);
====================

CREATE TABLE `products` (
`products_id` int(11) NOT NULL AUTO_INCREMENT,
`products_quantity` int(4) NOT NULL,
`products_model` varchar(13) DEFAULT NULL,
`products_image` varchar(64) DEFAULT NULL,
`products_price` decimal(15,4) NOT NULL,
`products_date_added` datetime NOT NULL,
`products_last_modified` datetime DEFAULT NULL,
`products_date_available` datetime DEFAULT NULL,
`products_weight` decimal(5,2) NOT NULL,
`products_status` tinyint(1) NOT NULL,
`products_tax_class_id` int(11) NOT NULL,
`manufacturers_id` int(11) DEFAULT NULL,
`products_ordered` int(11) NOT NULL DEFAULT '0',
`products_type` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`products_id`),
UNIQUE KEY `products_model` (`products_model`),
KEY `idx_products_model` (`products_model`),
KEY `idx_products_date_added` (`products_date_added`)
) TYPE=MyISAM CHARACTER SET=utf8;
================================================
The laterone is what I created, the first one is the backup version. They should be the same.
Strangly, the last line about character set is alyaws not
included in the backup? Perhapse a problem of the backup program?

Re: UPDATE but SQL said duplicate key

Posted: Sun Jun 24, 2012 12:20 am
by wvoyance
SHOWCREATETABLE products

get


CREATE TABLE `products` (
`products_id` int(11) NOT NULL AUTO_INCREMENT,
`products_quantity` int(4) NOT NULL,
`products_model` varchar(13) DEFAULT NULL,
`products_image` varchar(64) DEFAULT NULL,
`products_price` decimal(15,4) NOT NULL,
`products_date_added` datetime NOT NULL,
`products_last_modified` datetime DEFAULT NULL,
`products_date_available` datetime DEFAULT NULL,
`products_weight` decimal(5,2) NOT NULL,
`products_status` tinyint(1) NOT NULL,
`products_tax_class_id` int(11) NOT NULL,
`manufacturers_id` int(11) DEFAULT NULL,
`products_ordered` int(11) NOT NULL DEFAULT '0',
`products_type` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`products_id`),
UNIQUE KEY `products_id` (`products_id`),
UNIQUE KEY `products_id_2` (`products_id`),
UNIQUE KEY `products_model` (`products_model`),
KEY `idx_products_model` (`products_model`),
KEY `idx_products_date_added` (`products_date_added`),
FULLTEXT KEY `products_model_2` (`products_model`),
FULLTEXT KEY `products_model_3` (`products_model`),
FULLTEXT KEY `products_model_4` (`products_model`),
FULLTEXT KEY `products_image` (`products_image`)
) ENGINE=MyISAM AUTO_INCREMENT=1322 DEFAULT CHARSET=utf8

Re: UPDATE but SQL said duplicate key

Posted: Sun Jun 24, 2012 1:13 am
by requinix
wvoyance wrote:you replied with product_model="" is that because you don't know what should the inside be?
I replied with that because I know what's inside and what's inside is nothing. It's even right there in the query.
wvoyance wrote:On the other had, the error message had only one quotation mark.
Not the one you posted.

Code: Select all

1062 - Duplicate entry '' for key 'products_model'

Code: Select all

PRIMARY KEY (`products_id`),
UNIQUE KEY `products_id` (`products_id`),
UNIQUE KEY `products_id_2` (`products_id`),
UNIQUE KEY `products_model` (`products_model`),
KEY `idx_products_model` (`products_model`),
KEY `idx_products_date_added` (`products_date_added`),
FULLTEXT KEY `products_model_2` (`products_model`),
FULLTEXT KEY `products_model_3` (`products_model`),
FULLTEXT KEY `products_model_4` (`products_model`),
FULLTEXT KEY `products_image` (`products_image`)
That's a lot of identical keys. How much time have you spent mucking around with the table after you created it?

Re: UPDATE but SQL said duplicate key

Posted: Sun Jun 24, 2012 3:14 am
by wvoyance
requinix wrote:
wvoyance wrote:you replied with product_model="" is that because you don't know what should the inside be?
I replied with that because I know what's inside and what's inside is nothing. It's even right there in the query.
wvoyance wrote:On the other had, the error message had only one quotation mark.
Not the one you posted.

Code: Select all

1062 - Duplicate entry '' for key 'products_model'

Code: Select all

PRIMARY KEY (`products_id`),
UNIQUE KEY `products_id` (`products_id`),
UNIQUE KEY `products_id_2` (`products_id`),
UNIQUE KEY `products_model` (`products_model`),
KEY `idx_products_model` (`products_model`),
KEY `idx_products_date_added` (`products_date_added`),
FULLTEXT KEY `products_model_2` (`products_model`),
FULLTEXT KEY `products_model_3` (`products_model`),
FULLTEXT KEY `products_model_4` (`products_model`),
FULLTEXT KEY `products_image` (`products_image`)
That's a lot of identical keys. How much time have you spent mucking around with the table after you created it?
Hahahhh I am only aWARE I clicked two or 3 fulltextt that why I asked you the other question.
It only need one mouse down for one key.
I don't know when did I created the others


I have removed most of them....still testing whether will that get right......