Задача такая.
таблица пользователей
CREATE TABLE `users` (
id INT UNIQUE AUTO_INCREMENT NOT NULL,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
login VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
credit_number BIGINT UNIQUE NOT NULL,
telephone VARCHAR(50) UNIQUE NOT NULL,
date_reg DATE NOT NULL,
role VARCHAR(50) NOT NULL,
status VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (`role`) REFERENCES `authorities` (`username`),
FOREIGN KEY (`status`) REFERENCES `status` (`status_user`)
);
здесь поле 'role' должно обращаться к таблице 'authorities' и полю `authority`
#Роли безопасности клиентов
create table `authorities` (
id INT UNIQUE AUTO_INCREMENT NOT NULL,
username varchar(50) UNIQUE NOT NULL,
authority varchar(50) not null,
PRIMARY KEY (`username`),
constraint fk_authorities_users foreign key(authority) references users(role)
ON DELETE CASCADE
ON UPDATE CASCADE
);
поле 'status' должно обращаться к таблице 'status' и полю `status_user`
# таблица статуса пользователя
CREATE TABLE `status` (
`id` INT UNIQUE AUTO_INCREMENT NOT NULL,
`status_user` VARCHAR(30) NOT NULL,
PRIMARY KEY (`status_user`),
constraint fk_status_users foreign key(status_user) references `users`(status)
ON DELETE CASCADE
ON UPDATE CASCADE
);
то есть один пользователь может иметь несколько ролей безопасности
и одна роль безопасности может принадлежать многим пользователям
а вот для таблицы `status` пользователь может иметь какой-то один статус и один какой-то статус может принадлежать к нескольким пользователем
Как это должно быть правильно описано ?
вот полный код
DROP SCHEMA IF EXISTS `super_cars`;
CREATE SCHEMA IF NOT EXISTS `super_cars`;
USE `super_cars`;
CREATE TABLE `users` (
id INT UNIQUE AUTO_INCREMENT NOT NULL,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
login VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
credit_number BIGINT UNIQUE NOT NULL,
telephone VARCHAR(50) UNIQUE NOT NULL,
date_reg DATE NOT NULL,
role VARCHAR(50) NOT NULL,
status VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (`role`) REFERENCES `authorities` (`username`),
FOREIGN KEY (`status`) REFERENCES `status` (`status_user`)
);
#Роли безопасности клиентов
create table `authorities` (
id INT UNIQUE AUTO_INCREMENT NOT NULL,
username varchar(50) UNIQUE NOT NULL,
authority varchar(50) not null,
PRIMARY KEY (`username`),
constraint fk_authorities_users foreign key(authority) references users(role)
ON DELETE CASCADE
ON UPDATE CASCADE
);
# таблица статуса пользователя
CREATE TABLE `status` (
`id` INT UNIQUE AUTO_INCREMENT NOT NULL,
`status_user` VARCHAR(30) NOT NULL,
PRIMARY KEY (`status_user`),
constraint fk_status_users foreign key(status_user) references `users`(status)
ON DELETE CASCADE
ON UPDATE CASCADE
);
#Для хранения маркеров безопасности для клиентов, которые прошли аутентификацию
# и активировали опцию remember-me (используется Spring Security)
CREATE TABLE IF NOT EXISTS `persistent_logins` (
username VARCHAR(64) NOT NULL,
series VARCHAR(64) NOT NULL,
token VARCHAR(64) NOT NULL,
last_used TIMESTAMP NOT NULL,
PRIMARY KEY (series)
);
#заполнение таблицы
INSERT INTO users (
`first_name`, `last_name`, `login`, `password`, `email`,
`credit_number`, `telephone`, `date_reg`, `role`, `status`
)
VALUES
('Анна', 'Лунина', 'a.lunina', 'pass', 'a.lunina@mail.com',
145689997676, '11111111111', '2018-05-10', 'ROLE_ADMIN', 'enabled'),
('Aлекс', 'Королев', 'a.korolev', 'pass', 'a.korolev@mail.com',
14568934397466, '11123321111', '2018-05-11', 'ROLE_USER', 'enabled'),
('Роман', 'Лавров', 'r.lavrov', 'pass', 'r.lavrov@mail.com',
14568934397766, '11123321114', '2018-05-12', 'ROLE_USER', 'enabled');
#заполнение таблицы
INSERT INTO `status` (
`status_user`
)
VALUES
('enabled'),
('blocked'),
('disabled');
#заполнение таблицы
INSERT INTO `authorities` (
`username`,
`authority`
)
VALUES
( 'a.lunina', 'ROLE_ADMIN'),
( 'a.korolev' , 'ROLE_USER'),
('r.lavrov', 'ROLE_USER' );
#create unique index ix_auth_username on authorities (username,authority);
#просмотр таблиц
#SHOW TABLES;
укажите пожайлуста на ошибки и поясните.
И привязка полей должна быть только между полями с одинаковым типом данных ?
Я хотел бы избежать привязку к id, если это возможно, например если поле 'role' из таблицы `users` обращается к таблице authorities , то в поле 'role' я хотел бы видить название роли безопасности, а не ее id из таблицы `authorities`
спасибо.
Отредактированно jron (20.05.2019 17:19:37)
