forked from BRETT/BRETT
18 lines
1.1 KiB
SQL
18 lines
1.1 KiB
SQL
CREATE TABLE `roles` (
|
|
`id` bigint unsigned AUTO_INCREMENT NOT NULL,
|
|
`name` varchar(50) NOT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT (now()),
|
|
CONSTRAINT `roles_id` PRIMARY KEY(`id`),
|
|
CONSTRAINT `roles_name_unique` UNIQUE(`name`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `user_roles` (
|
|
`user_id` bigint unsigned NOT NULL,
|
|
`role_id` bigint unsigned NOT NULL,
|
|
CONSTRAINT `user_roles_user_id_role_id_pk` PRIMARY KEY(`user_id`,`role_id`)
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE `user_roles` ADD CONSTRAINT `user_roles_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE `user_roles` ADD CONSTRAINT `user_roles_role_id_roles_id_fk` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
INSERT INTO `roles` (`name`) VALUES ('admin');--> statement-breakpoint
|
|
INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`) SELECT `u`.`id`, `r`.`id` FROM `users` `u`, `roles` `r` WHERE `r`.`name` = 'admin' ORDER BY `u`.`id` LIMIT 1; |