· 9 years ago · Oct 26, 2016, 08:32 AM
1DROP TABLE IF EXISTS user_roles;
2DROP TABLE IF EXISTS contacts;
3DROP TABLE IF EXISTS users;
4
5CREATE TABLE IF NOT EXISTS users
6(
7 id INTEGER AUTO_INCREMENT,
8 login VARCHAR(45) NOT NULL,
9 password VARCHAR(45) NOT NULL,
10 full_name VARCHAR(100) NOT NULL,
11 PRIMARY KEY (id)
12);
13CREATE UNIQUE INDEX users_unique_login_idx ON users (login);
14
15CREATE TABLE IF NOT EXISTS user_roles
16(
17 user_id INTEGER NOT NULL,
18 role VARCHAR(45),
19 CONSTRAINT user_roles_idx UNIQUE (user_id, role),
20 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
21);
22
23CREATE TABLE IF NOT EXISTS contacts (
24 id INTEGER AUTO_INCREMENT,
25 user_id INTEGER NOT NULL,
26 first_name VARCHAR(45) NOT NULL,
27 last_name VARCHAR(45) NOT NULL,
28 patronymic VARCHAR(45) NOT NULL,
29 mobile_phone_number VARCHAR(15),
30 home_phone_number VARCHAR(15),
31 address VARCHAR(45),
32 email VARCHAR(30),
33 PRIMARY KEY (id),
34 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
35);
36CREATE UNIQUE INDEX unique_idx ON contacts (user_id);
37
38DELETE FROM lardi.users;
39DELETE FROM lardi.user_roles;
40DELETE FROM lardi.contacts;
41
42INSERT INTO lardi.users
43 (login,password,full_name) VALUES
44 ('Bill', '112233', 'user'),
45 ('John', '112233', 'user'),
46 ('Mark', '112233', 'user');
47
48INSERT INTO lardi.user_roles
49 (role,user_id) VALUES
50 ('USER_ROLE',1),
51 ('USER_ROLE',2),
52 ('ADMIN_ROLE',3);
53
54INSERT INTO lardi.contacts
55(first_name, last_name, patronymic, mobile_phone_number, home_phone_number, address, email, user_id) VALUES
56 ('Bill','Gates','','+380(66)1234567','','USA','bill@gmail.com', 1),
57 ('Mark','Zukenberg','','+380(66)9876543','+380(44)1122334','USA','mark@gmail.com', 1),
58 ('Barak','Obama','','+380(99)1234567','','USA','barak@gmail.com', 1),
59 ('Michel','Obama','','+380(99)9876543','','USA','michel@gmail.com', 1),
60 ('David','Camaron','','+380(50)5557799','+380(44)0000009','UK','david@gmail.com', 2),
61 ('Steve','Jobs','','+380(00)1100999','','USA','steve@gmail.com', 2),
62 ('Tim','Kuk','','+380(00)2244888','','USA','tim@gmail.com', 2),
63 ('Jim','Carry','','+380(69)8881188','+380(44)1111119','USA','jim@gmail.com', 3),
64 ('David','Backham','','+380(67)90000001','','UK','david@gmail.com', 3);