By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users
(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(15) NOT NULL UNIQUE,
password VARCHAR(200) NOT NULL,
name VARCHAR(15) NOT NULL,
last_name VARCHAR(25) NOT NULL,
department VARCHAR(50) NOT NULL,
salary INT,
age TINYINT NOT NULL,
email VARCHAR(50),
enabled TINYINT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE roles
(
id INT NOT NULL AUTO_INCREMENT,
role VARCHAR(15) NOT NULL UNIQUE,
PRIMARY KEY (id)
);
CREATE TABLE user_role
(
user_id INT NOT NULL,
username VARCHAR(15) NOT NULL UNIQUE,
role_id INT NOT NULL,
role VARCHAR(15) NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users (id),
FOREIGN KEY (role_id) REFERENCES roles (id)
);
INSERT INTO users(username, password, name, last_name, department, salary, age, email, enabled)
SELECT *
FROM (VALUES ROW('mickey_m', '$2y$10$i5SJc2KAriHGn7Dz2rRQHuQ3JfBxlzMaPVKP1YdEJCukryDY9NbVC',
'Mickey', 'Mouse', 'sales', 180000, 95, 'mickey_m@gmail.com', 1),
ROW('donald_d', '$2y$10$E6SHpAN0aZGQ43HAO.TPiO27kDKXOGIgDc8xWDJdl2Prn2wzQzz5y',
'Donald', 'Duck', 'information technology', 190000, 89, 'donald_d@outlook.com', 1),
ROW('scrooge_m', '$2a$10$ycVpl2BSD6o19wa3xXtmrOxc8qZjwoIk.e3oZqgQBOE.3NHANAYqa',
'Scrooge', 'McDuck', 'board of directors', 700000, 76, 'scrooge@gmail.com', 1),
ROW('minerva_m', '$2y$10$sLgPImwI1WR5KZ98AFPHHut6MvbDoOdBnDC232oLRfejVsganoVyC',
'Minerva', 'Mouse', 'sales', 180000, 95, 'minnie_m@outlook.com', 1),
ROW('goofus_d', '$2y$10$uRP3KtW/yviK.H2VazCECOEQdZxZUZNqHqy/bL4kZgmaBCdprNeE2',
'Goofus', 'Dawg', 'information technology', 190000, 91, 'goofy@gmail.com', 1),
ROW('daisy_d', '$2a$10$mYfix0eIUMIWeTz6kM5.vuI/1demwzsvnY/mSgp5W6rcQVjc3Eyny',
'Daisy', 'Duck', 'human resources', 165000, 86, 'daisy@outlook.com', 1)) sample_rows
WHERE NOT EXISTS(SELECT NULL FROM users);
INSERT IGNORE INTO roles(role)
VALUES ('USER'),
('ADMIN');
INSERT IGNORE INTO user_role
WITH user_to_role(user, role) AS ( VALUES ROW('mickey_m', 'USER'),
ROW('donald_d', 'USER'),
ROW('scrooge_m', 'USER'),
ROW('scrooge_m', 'ADMIN'),
ROW('minerva_m', 'USER'),
ROW('goofus_d', 'USER'),
ROW('daisy_d', 'USER'))
SELECT users.id, users.username, roles.id, roles.role FROM users JOIN
roles ON users.username = user_to_role.user AND roles.role = user_to_role.role;
Records: 6 Duplicates: 0 Warnings: 0
Records: 2 Duplicates: 0 Warnings: 0
Unknown column 'user_to_role.user' in 'on clause'