I have the following code
CREATE TABLE IF NOT EXISTS `abuses` (
`abuse_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0',
`abuser_username` varchar(100) NOT NULL DEFAULT '',
`comment` text NOT NULL,
`reg_date` int(11) NOT NULL DEFAULT '0',
`id` int(11) NOT NULL,
PRIMARY KEY (`abuse_id`),
KEY `reg_date` (`reg_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Table with abuse reports' AUTO_INCREMENT=2 ;
this table already exists in the database, but when i import an sql file with phpmyadmin, the following error occurs
--
-- Dumping data for table `probid_abuses`
--
INSERT INTO `abuses` ( `abuse_id` , `user_id` , `abuser_username` , `comment` , `reg_date` , `auction_id` )
VALUES ( 1, 100020, 'artictundra', 'I placed a bid for it more than an hour ago. It is still active. I thought I was supposed to get an email after 15 minutes.', 1338052850, 108625 ) ;
#1062 - Duplicate entry '1' for key 'PRIMARY'
i thought because it already exists it won't attempt to create it, why is it behaving as such?
This question is related to
mysql
phpmyadmin
If you really want to insert this record, remove the `abuse_id`
field and the corresponding value from the INSERT
statement :
INSERT INTO `abuses` ( `user_id` , `abuser_username` , `comment` , `reg_date` , `auction_id` )
VALUES ( 100020, 'artictundra', 'I placed a bid for it more than an hour ago. It is still active. I thought I was supposed to get an email after 15 minutes.', 1338052850, 108625 ) ;
Depending on what you want to accomplish, you might replace INSERT with INSERT IGNORE in your file. This will avoid generating an error for the rows that you are trying to insert and already exist.
it is because you already defined the 'abuse_id' as auto increment, then there is no need to insert its value. it will be inserted automatically. the error comes because you are inserting 1 many times that is duplication of data. the primary key should be unique. should not be repeated.
the thing you have to do is to change your insertion query as below
INSERT INTO `abuses` ( `user_id` , `abuser_username` , `comment` , `reg_date` , `auction_id` )
VALUES ( 100020, 'artictundra', 'I placed a bid for it more than an hour ago. It is still active. I thought I was supposed to get an email after 15 minutes.', 1338052850, 108625 ) ;
On the CREATE TABLE,
The AUTO_INCREMENT of abuse_id is set to 2. MySQL now thinks 1 already exists.
With the INSERT statement you are trying to insert abuse_id with record 1. Please set AUTO_INCREMENT on CREATE_TABLE to 1 and try again.
Otherwise set the abuse_id in the INSERT statement to 'NULL'.
How can i resolve this?
In your case, the first value to insert must be NULL, because it's AUTO_INCREMENT.
Source: Stackoverflow.com