CODEWITHSUNDEEP
mysqlforeign-keys
ControlAltDel
ControlAltDel

Reputation: 35086

MySQL: Migrating Tables to a new DB - foreign keys

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)?

Upvotes: 1

Views: 607

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562558

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.

Upvotes: 1

Related Questions