MySQL: Migrating Tables to a new DB - foreign keys

Question

I learned yesterday (via the magic of Google) that I can have foreign key references on values in another database. (MySQL InnoDB foreign key between different databases) I have two projects (currently set up in one database) and I'd like to move them into 3 databases: 1 for each project, and 1 for admin and user information (which is shared by both projects). I'd like to do this to make any future migration easier

My question is: Is it possible to move my admin tables from the current database into a new db, copying its data and automatically updating the foreign keys in my project dbs? The question on auto-updating foreign keys is really question I am seeking an answer on... Is there a way to do this in SQL (or some other way)?

'values in another database.' - I don't think so https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html doesn't have a db name option and specifically states 'MySQL supports foreign keys, which permit cross-referencing related data across tables' - do you have a link to magicland? — P.Salmon, Jan 28 '22 at 16:52
@P.Salmon I have included the StackOverflow question where I found this information — ControlAltDel, Jan 28 '22 at 16:55

score 1 · Accepted Answer · answered Jan 28 '22 at 17:00

It is easy enough to test:

mysql> use test
Database changed

mysql> create table parent (id int primary key );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into parent values (42);
Query OK, 1 row affected (0.02 sec)

mysql> create table child (parent_id int, foreign key (parent_id) references parent(id));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into child values (42);
Query OK, 1 row affected (0.02 sec)

mysql> rename table child to test2.child;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table test2.child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `parent_id` int(11) DEFAULT NULL,
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `test`.`parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Notice that the constraint in child now qualifies the referenced table as test.parent.

MySQL: Migrating Tables to a new DB - foreign keys

1 Answers1