SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 20.05.2019 17:14:23

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 44

Создание таблиц с отношением Many-to-many и one-to-many

Задача такая.

таблица пользователей

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)

Неактивен

 

#2 22.05.2019 10:54:26

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 44

Re: Создание таблиц с отношением Many-to-many и one-to-many

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,
  PRIMARY KEY (login)
);

#заполнение таблицы
INSERT INTO users (
  `first_name`, `last_name`, `login`, `password`, `email`,
  `credit_number`, `telephone`,  `date_reg`
)
VALUES
  ('Анна', 'Лунина', 'a.lunina', 'pass', 'a.lunina@mail.com',
   145689997676, '11111111111', '2018-05-10'),
  ('Aлекс', 'Королев', 'a.korolev', 'pass', 'a.korolev@mail.com',
   14568934397466, '11123321111', '2018-05-11'),
  ('Роман', 'Лавров', 'r.lavrov', 'pass', 'r.lavrov@mail.com',
   14568934397766, '11123321114', '2018-05-12');



#Роли безопасности клиентов
create table `authorities` (

  id INT UNIQUE AUTO_INCREMENT NOT NULL,
  username varchar(50) UNIQUE NOT NULL,
  authority varchar(50) not null,
  PRIMARY KEY (`id`),
  constraint fk_authorities_users foreign key(username) references `users`(login)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

#заполнение таблицы
INSERT INTO `authorities` (
  `username`,
  `authority`
)
VALUES
  ( 'a.lunina', 'ROLE_ADMIN'),
  (  'a.korolev' , 'ROLE_USER'),
  (  'r.lavrov', 'ROLE_USER' );

# таблица статуса пользователя
CREATE TABLE `status` (
  `id`          INT UNIQUE AUTO_INCREMENT NOT NULL,
  `username` varchar(50) UNIQUE NOT NULL,
  `status_user` VARCHAR(30)               NOT NULL,
  PRIMARY KEY (`id`),
  constraint fk_status_users foreign key(username) references `users`(login)
    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 `status` (
  `username`,
  `status_user`
)
VALUES
  ( 'a.lunina','enabled'),
  ('a.korolev' , 'blocked'),
  ('r.lavrov','disabled');


#просмотр таблиц
SHOW TABLES;


решено

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson