MYSQL, Error 1062

Question

I am having an issue inserting values into a table. I am receiving the Error Code: 1062 error message.

Using SHOW CREATE TABLE roof_panel_width; the CREATE code for the table in question was returned.

CREATE TABLE `roof_panel_width` (
    `rpw_id` int(11) NOT NULL AUTO_INCREMENT,
    `width_in_inches` decimal(2,1) NOT NULL,
    PRIMARY KEY (`rpw_id`),
    UNIQUE KEY `width_in_inches` (`width_in_inches`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1'

The error code is returned when using the follow code.

INSERT INTO `roof_panel_width` (`width_in_inches`)
VALUES (14),(16),(18),(24),(29.5),(36);

The full error that is returned is

Error Code: 1062. Duplicate entry '9.9' for key 'width_in_inches'

I am not trying to insert the value 9.9, and the table will only contain the above values. At the moment is it empty. I have also tried dropping the table and creating new. There was no change in the error code. Any help would be appreciated.

What mysql version are you using? – Mureinik Jun 18 '18 at 16:25 — Mureinik, Jun 18 '18 at 16:25

score 1 · Accepted Answer · answered Jun 18 '18 at 16:25

1

The issue is that by setting a column to be DECIMAL(2,1) you are allowing a maximum of 2 parts to the number, so 1 for the whole, and 1 for the decimal. When you're trying to insert 14 then 16, it's changing this to be the maximum 9.9 for both, and causing a duplicate.

If you change the decimal column to (3, 1), then you can go up to 99.9

answered Jun 18 '18 at 16:25

gabe3886

4,235
3
27
31

I was starting to type the same thing. You beat me by 30 seconds. – manassehkatz-Moving 2 Codidact Jun 18 '18 at 16:26
Thanks that did it. I misunderstood how to properly define the type. – Brook Julias Jun 18 '18 at 16:27

score 0 · Answer 2 · answered Jun 18 '18 at 16:26

0

Change DECIMAL(2,1) to DECIMAL(3,1) to hold values up to 99.9 for column width_in_inches. For holding higher values you need to adjust precision and scale accordingly for:

DECIMAL(precision, scale)

answered Jun 18 '18 at 16:26

Kamil Gosciminski

16,547
8
49
72

MYSQL, Error 1062

2 Answers2