17 lines
532 B
SQL
17 lines
532 B
SQL
-- Add role_id column
|
|
ALTER TABLE `users` ADD COLUMN `role_id` INT(11) NULL AFTER `password`;
|
|
|
|
-- Update role_id from existing role name
|
|
UPDATE `users` u
|
|
JOIN `roles` r ON u.role = r.name
|
|
SET u.role_id = r.id;
|
|
|
|
-- Make role_id not nullable
|
|
ALTER TABLE `users` MODIFY `role_id` INT(11) NOT NULL;
|
|
|
|
-- Add foreign key constraint
|
|
ALTER TABLE `users` ADD CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- Drop the old role column
|
|
ALTER TABLE `users` DROP COLUMN `role`;
|