Untitled

 avatar
unknown
mysql
a month ago
1.8 kB
4
Indexable
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `author` (
  `author_id` int(11) NOT NULL,
  `name_surname` varchar(100) NOT NULL,
  `author_email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `author` (`author_id`, `name_surname`, `author_email`) VALUES
(3, 'Ece Gokce', 'ece@mail.com'),
(2, 'Yavuz Behic', 'yavuz@mail.com');

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name_surname` varchar(100) NOT NULL,
  `user_email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `users` (`user_id`, `password`, `name_surname`, `user_email`) VALUES
(2, 'scrypt:32768:8:1$lEfiqP8uoyu4H52g$32126e909f5c55049e473389c705e5327e1b515903b9cce094101779a69e93e094b164d63190cd00b407394197ef35867025f5da7a007c2c14eeb7209f19914b', 'Yavuz Behic', 'yavuz@mail.com'),
(3, 'scrypt:32768:8:1$5Ozn9Mu0DHtrr0ox$43a4db16f78137bd273e8d8d3a5bf2fcb75a652827b00c2a291047efa618714dfbd6c99348c1dbe061235f9135ce328f0686f55d8f815ab76b70b6ee09309914', 'Ece Gokce', 'ece@mail.com');


ALTER TABLE `author`
  ADD PRIMARY KEY (`author_id`),
  ADD UNIQUE KEY `UQ_author_email_name` (`author_email`,`name_surname`);

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `user_email_2` (`user_email`,`name_surname`);


ALTER TABLE `author`
  MODIFY `author_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;


ALTER TABLE `users`
  ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`user_email`,`name_surname`) REFERENCES `author` (`author_email`, `name_surname`) ON DELETE CASCADE;
COMMIT;
Leave a Comment