Re: Update foreign key when primary key changes, when passing data from SQLite to MySQL
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...