Error: Duplicate entry '1' for key 1

Hi,
When i am inserting date in just created table it returns following error: Duplicate entry '1' for key 1. The primary key is set to auto_increament. What could cause the problem and where should i look(beginer do not know where)? Here is the code:
//inset data into "movie" table
$insert="INSERT INTO movie (movie_id, movie_name, movie_type, movie_year, movie_leadactor, movie_director) VALUES (1, 'Bruce Almighty', 5, 2003, 1, 2),
(2, 'Office Space', 5, 1999, 5, 6),
(3, 'Grand Canyon', 2, 1991, 4, 3)";
$results = mysql_query($insert)
or die(mysql_error());
########
for creating table:
//creat movie table
$movie="CREATE TABLE movie (
movie_id INT(11) NOT NULL AUTO_INCREMENT,
movie_name VARCHAR(255) NOT NULL,
movie_type TINYINT(2) NOT NULL DEFAULT 0,
movie_year INT(4) NOT NULL DEFAULT 0,
movie_leadactor INT(11) NOT NULL DEFAULT 0,
movie_director INT(11) NOT NULL DEFAULT 0,
PRIMARY KEY (movie_id),
KEY movie_type (movie_type,movie_year)
) TYPE=MyISAM AUTO_INCREMENT=4 ";

Thank's
m
margotskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZylochCommented:
Hi margotsk,

Primary keys are unique, meaning that they must always have different values. If you want to change the row that has movie_id of 1, use this:

"UPDATE movie SET movie_name='Bruce Almighty', movie_type=5, movie_year=2003, movie_leadactor=1, movie_director=2 WHERE movie_id=1"

for the first one, (for example)

Regards,
Zyloch
0
sigmaconCommented:
margotsk, I ran your queries, they both worked fine. If you run the insert statement TWICE, it gives the error you are describing. Run

select * from movie

first before you try to insert data to see that's not already in there.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UmeshMySQL Principle Technical Support EngineerCommented:
Hi,

in your SQL statement..

AUTO_INCREMENT=4

This means your auto increment should start from 4 or so..

try with this..

//inset data into "movie" table
$insert="INSERT INTO movie (movie_id, movie_name, movie_type, movie_year, movie_leadactor, movie_director) VALUES ('','Bruce Almighty', 5, 2003, 1, 2),
('','Office Space', 5, 1999, 5, 6),
('','Grand Canyon', 2, 1991, 4, 3)";



0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

JakobACommented:
The standad way is to just not mention the auto-increment field at all in the INSERT statement:

INSERT INTO movie
            ( movie_name,      movie_type, movie_year, movie_leadactor, movie_director)
VALUES ( 'Bruce Almighty', 5,               2003,          1,                       2                    )
0
margotskAuthor Commented:
Thank's guys for getting back,
it was the case as Sigmacon pointed out and it happene because at the first time, i was trying to insert separate entries for each table(total 3 table) at the same time using php code, but it return error after insereting first two and failing on last one. So, afterwards, when i was trying to run each insertion separetly it return this error, because i had the entries already inserted.
Thank's
All the best,
margotsk
0
zzapperCommented:
Thanks this solution helped me!!
This is one of those brilliantly obscure error messages.
It is actually saying "You are trying to re-insert a record that already exists (duplicate key).

In my case I'd duplicated a table, but the Primary Key autoincrement had not been carried across.
I assigned the PK autoincrement and all was OK

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.