Stumped with duplicate key error.

[IBazz]

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?

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

[IBazz]

It turns out that it isn't a mysql problem.

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

bazz

[IBazz]

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.

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

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

[IBazz]

solved the mysql issue. now just a perl one :|

bazz

[guelphdad]

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.

[IBazz]

Thank you guelphdad.

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

bazz