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.

Answer 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

Answer 2

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)