Change master table PK and update related table FK (changing PK from Autoincrement to UUID on Mysql)
- by eleonzx
I have two related tables: Groups and Clients. Clients belongs to Groups so I have a foreign key "group_id" that references the group a client belongs to.
I'm changing the Group id from an autoincrement to a UUID. So what I need is to generate a UUID for each Group and update the Clients table at once to reflect the changes and keep the records related.
Is there a way to do this with multiple-table update on MySQL?
Adding tables definitions for clarification.
CREATE TABLE `groups` (
`id` char(36) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
CREATE TABLE `clients` (
`id` char(36) NOT NULL,
`name` varchar(255) NOT NULL,
`group_id` char(36) DEFAULT NULL,
`active` tinyint(1) DEFAULT '1'
PRIMARY KEY (`id`),
KEY `fkgp` (`group_id`),
CONSTRAINT `fkgp` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
