SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stumped with duplicate key error.

    Hello,

    I am building a db, where there is a table with all the data (loaded from csv file) and I am writing scripts to copy speciifc cols from here to properly normalised tables.

    The data queried from this table prints as intended, to the web page. However, when I try to input it to the db table, it errors on id 44 where none of the data in row 44 is a duplicate likewise, row 43 or 45.

    the error is this

    DBD::mysql::st execute failed: Duplicate entry '1-2070395153-0000-00-00' for key 1 at transfer_from_main_table_to_others.pl line 59
    Is my table structure causing it?

    Code mysql:
    CREATE TABLE IF NOT EXISTS customer_machines
    ( customer_id int not null
    , serial_number int not null
    , date_of_purchase date not null
    , last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    , last_updated_by varchar (64) default NULL
    , PRIMARY KEY (customer_id,serial_number,date_of_purchase)
    , KEY second_ix (serial_number,customer_id)
    , CONSTRAINT customer_fk 
        FOREIGN KEY (customer_id) 
          REFERENCES customer_details (customer_id) 
    ) ENGINE=INNODB DEFAULT CHARSET=LATIN1;

    What am i overlooking?

    Any pointers very welcome.

    bazz

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It turns out that it isn't a mysql problem.

    My scripting language isn't doing as I thought for some reason.

    bazz

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, we're back to a mysql error.

    the following code will print to the screen, the actual values for each col. But when I tell it to insert to the db, it makes hundreds of records have the same $serial.

    Code:
    my $input_machine_data=$dbhconnect->prepare("INSERT into customer_machines 
                                 (  customer_id
                                 , serial_number
                                 , date_of_purchase
                                 , last_updated
                                 , last_updated_by
                                 )
                             values (?,?,?,'','' ) ")
                      or die "prepare Failed on: $srv\n\t$DBI::errstr\n";
    and it gives me an error of

    Code:
    Cannot add or update a child row: 
    a foreign key constraint fails 
    (`db_name/customer_machines`,
     CONSTRAINT `customer_fk` FOREIGN KEY (`customer_id`) 
    REFERENCES `customer_details` (`customer_id`)
    ) at transfer_from_main_table_to_others.pl line 71.
    I know what I am about to write seems to argue with mysql but, I have no duplicate rows. The customer_id PK is part of a composite key and so would be different anyway with the other values changing - wouldn't it?

    The customer_id is different in every record anyway since it is being retrieved from the source table.

    I must be missing something ~ perhaps that the customer_id value is 'sticking' just like the $serial var.



    bazz
    Last edited by IBazz; Feb 4, 2009 at 05:51.

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    solved the mysql issue. now just a perl one :|

    bazz

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    small suggestion if I may. when you post your code it is indented so we always have to scroll the window left and right to read it.

    Here's a formatter if you want to use one: http://www.wangz.net/cgi-bin/pp/gsql.../sqlformat.tpl you can drop your code into it and have it easily format.

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guelphdad.

    Although I didn't get formatter to work, I have amended it

    bazz


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •