Re: Foreign key must always be the primary key in the other table?

[Nuno MIguel]

Here is an example: In the table "organization_smartphone_A" I created the field "_id_org", that will be some kind of auxiliary primary key, relate to the foreign key in the other table.

---------------------

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

insert into organization_smartphone_A (_id_org, organization_name, organization_email)
values(_id_org_value, 'latitude zero','[e-mail]'


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

insert into roadbook_smartphone_A (rb_name, rb_date, rowid_organization)
values('rb jeep','19-7-2016' , 1)

------------------------------------------------

The values for the new field (_id_org_value) in the table organization_smartphone_A will be created using the primary key value of the last record in the table(+1), adding to this value for example the actual date and the smartphone IMEI, so that it will be created a unique value.

Example: _id_org_value= 5_24-07-2016_3573765765435
where:
- 5 is calculated adding 1 to the primary key of the previous record in the table
- 24-07-2016 is the date where the record was created
- 3573765765435 is the smartphone IMEI

The value will be created in the java application, every time a new record is created.

This way, every record will have a different identification and when the values are passed to the main database in the web, even if the value of the primary key changes, the value of this field and respective foreign key will never change.

What do you think?

Any suggestions?