CODEWITHSUNDEEP
phpmysql
GusDeCooL
GusDeCooL

Reputation: 5758

MySQL Export Import with ID auto_increment for Foreign Key

Consider i have 2 database that have same field.
first database is for export data,
second database act as copy have database

Database 1 will export database in file format csv that output from PHP Script.
Database 2 will import database from php script.

There is two table is in each database that relations with foreign key.

table_transaction

create table `table_transaction` (
    `id` int auto_increment primary key
    `date` DATE default now()
) engine = innoDB;

sample data

id | date 
1  | 2012-12-31
2  | 2012-12-30
3  | 2012-12-29

table_transaction_product

create table `table_transaction_product` (
    `id` int auto_increment primary key
    `product` string NOT NULL default '' /* Product Name */
    `fk_transaction` int auto_increment NOT NULL

     foreign key (`fk_transaction`)
         references table_transaction(`id`)
         on update cascade
         on delete cascade
) engine = innoDB;

sample data

id | product     | fk_transaction
1  | shampoo     | 1
2  | soap        | 1
3  | conditioner | 1

And this is sample exported CSV from database 1 and will be imported to table 2, that exporting transaction id 1.

insert into table_transaction (id, date) values (1, '2012-12-31');
insert into table_transaction_product (id, product, fk_transaction) 
    values 
        (1, 'shampoo', 1),
        (2, 'soap', 1),
        (3, 'conditioner', 1)

Question
Since the ID both table is auto_increment. Isn't there will be any problem that i insert manually the table.id and crash the auto_increment mysql system? But if didn't input the ID and let's mysql decide it, then the foreign key will be not match. What should i do?

Thank you.

Upvotes: 0

Views: 1424

Answers (1)

Terence Johnson
Terence Johnson

Reputation: 1647

It shouldn't cause a problem, but I would recommend changing the table definitions on the second database to remove the auto_increment flag.

Upvotes: 1

Related Questions