Solved

Error: Duplicate entry '1' for key 1

Posted on 2004-10-23
18,183 Views
Last Modified: 2011-10-03
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
0
Question by:margotsk
    6 Comments
     
    LVL 36

    Expert Comment

    by:Zyloch
    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
     
    LVL 8

    Accepted Solution

    by:
    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
     
    LVL 26

    Expert Comment

    by:ushastry
    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
     
    LVL 15

    Expert Comment

    by:JakobA
    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
     

    Author Comment

    by:margotsk
    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
     

    Expert Comment

    by:zzapper
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

     Java Android Coding Bundle

    Whether you're an Apple user or Android addict, learning to code for the Android platform is an extremely valuable, in-demand skill. It all starts with Java, the language behind the apps and games that make Android the top platform it is today.

    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now