MySQL Forums
Forum List  »  Newbie

Re: Update foreign key when primary key changes, when passing data from SQLite to MySQL
Posted by: Nuno MIguel
Date: July 22, 2016 03:00PM

I can place 2 exemple tables (simplified a little):

CREATE TABLE IF NOT EXISTS `organization` (
`_id` int(11) NOT NULL AUTO_INCREMENT,
`organization_name` varchar(100),
`organization_email` varchar(100),
PRIMARY KEY (`_id`)
)



CREATE TABLE IF NOT EXISTS `roadbook` (
`_id` int(11) NOT NULL AUTO_INCREMENT,
`rb_name` varchar(100) ,
`rb_date` date,
`rowid_organization` integer,
PRIMARY KEY (`_id`),
FOREIGN KEY (rowid_organization) REFERENCES organization(_id) ON DELETE RESTRICT ON UPDATE CASCADE
)


the SQLIte database is in an app that is installed in 2 smartphones:

----------------- smartphone A -----------------

table `organization`
_id organization_name organization_email
1 latitude zero latitudezero@mail.com


table `roadbook`
_id rb_name rb_date rowid_organization
1 rb jipe 19-7-2016 1



----------------- smartphone B -----------------

table `organization`
_id organization_name organization_email
1 XPTO xpto@mail.com
2 AAAA aaaa@mail.com


table `roadbook`
_id rb_name rb_date rowid_organization
1 rb1 05-7-2016 1
2 rb2 16-7-2016 2
3 rb50 22-7-2016 1



1. smartphone A sends all the information to the MySQL database in the web:

----------------- database MySQL with the data from smartphone A ---------------

table `organization`
_id organization_name organization_email
1 latitude zero latitudezero@mail.com


table `roadbook`
_id rb_name rb_date rowid_organization
1 rb jipe 19-7-2016 1


2. smartphone B sends all the information to the MySQL database in the web, that already has the data from smartphone A:

----------------- database MySQL with the data from smartphone A and B ---------

table `organization`
_id organization_name organization_email
1 latitude zero latitudezero@mail.com
2 XPTO xpto@mail.com
3 AAAA aaaa@mail.com

table `roadbook`
_id rb_name rb_date rowid_organization
1 rb jipe 19-7-2016 1
2 rb1 05-7-2016 1
3 rb2 16-7-2016 2
4 rb50 22-7-2016 1

As you can see the id (primary key) from the data from smartphone B has to change, because it is "auto_increment"
Example of one problem: As you can see, originally the organization for rb2 had the foreign key 2 (organization AAAA), but because the foreign key doesn't change, now in the MySQL database de foreign key is 2, that refers to the organization XPTO, in the table organization, which is wrong...

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.