Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE IF EXISTS user_roles;
- DROP TABLE IF EXISTS contacts;
- DROP TABLE IF EXISTS users;
- CREATE TABLE IF NOT EXISTS users
- (
- id INTEGER AUTO_INCREMENT,
- login VARCHAR(45) NOT NULL,
- password VARCHAR(45) NOT NULL,
- full_name VARCHAR(100) NOT NULL,
- PRIMARY KEY (id)
- );
- CREATE UNIQUE INDEX users_unique_login_idx ON users (login);
- CREATE TABLE IF NOT EXISTS user_roles
- (
- user_id INTEGER NOT NULL,
- role VARCHAR(45),
- CONSTRAINT user_roles_idx UNIQUE (user_id, role),
- FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
- );
- CREATE TABLE IF NOT EXISTS contacts (
- id INTEGER AUTO_INCREMENT,
- user_id INTEGER NOT NULL,
- first_name VARCHAR(45) NOT NULL,
- last_name VARCHAR(45) NOT NULL,
- patronymic VARCHAR(45) NOT NULL,
- mobile_phone_number VARCHAR(15),
- home_phone_number VARCHAR(15),
- address VARCHAR(45),
- email VARCHAR(30),
- PRIMARY KEY (id),
- FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
- );
- CREATE UNIQUE INDEX unique_idx ON contacts (user_id);
- DELETE FROM lardi.users;
- DELETE FROM lardi.user_roles;
- DELETE FROM lardi.contacts;
- INSERT INTO lardi.users
- (login,password,full_name) VALUES
- ('Bill', '112233', 'user'),
- ('John', '112233', 'user'),
- ('Mark', '112233', 'user');
- INSERT INTO lardi.user_roles
- (role,user_id) VALUES
- ('USER_ROLE',1),
- ('USER_ROLE',2),
- ('ADMIN_ROLE',3);
- INSERT INTO lardi.contacts
- (first_name, last_name, patronymic, mobile_phone_number, home_phone_number, address, email, user_id) VALUES
- ('Bill','Gates','','+380(66)1234567','','USA','bill@gmail.com', 1),
- ('Mark','Zukenberg','','+380(66)9876543','+380(44)1122334','USA','mark@gmail.com', 1),
- ('Barak','Obama','','+380(99)1234567','','USA','barak@gmail.com', 1),
- ('Michel','Obama','','+380(99)9876543','','USA','michel@gmail.com', 1),
- ('David','Camaron','','+380(50)5557799','+380(44)0000009','UK','david@gmail.com', 2),
- ('Steve','Jobs','','+380(00)1100999','','USA','steve@gmail.com', 2),
- ('Tim','Kuk','','+380(00)2244888','','USA','tim@gmail.com', 2),
- ('Jim','Carry','','+380(69)8881188','+380(44)1111119','USA','jim@gmail.com', 3),
- ('David','Backham','','+380(67)90000001','','UK','david@gmail.com', 3);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement