COP4331-Group14-ContactManager/init_db.sql at main · jonathandieu/COP4331-Group14-ContactManager · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- Create database
CREATE DATABASE yellabook;
USE yellabook;

-- Create tables
CREATE TABLE users (
    login VARCHAR(64) NOT NULL DEFAULT '',
    password VARCHAR(64) NOT NULL DEFAULT '',
    name VARCHAR(255) NOT NULL DEFAULT '',

    PRIMARY KEY (login)
);

CREATE TABLE contacts (
    id INT NOT NULL AUTO_INCREMENT,
    login VARCHAR(64) NOT NULL DEFAULT '',
    cname VARCHAR(255) NOT NULL DEFAULT '',
    FOREIGN KEY (login) REFERENCES users (login)
		ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (login, cname),
    UNIQUE KEY (id)
);

CREATE TABLE emails (
    login VARCHAR(64) NOT NULL DEFAULT '',
    cname VARCHAR(255) NOT NULL DEFAULT '',
    type VARCHAR(64) NOT NULL DEFAULT 'home',
    address VARCHAR(255) NOT NULL DEFAULT '',
    FOREIGN KEY (login, cname) REFERENCES contacts (login, cname)
		ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (login, cname, address)
);

CREATE TABLE phones (
    login VARCHAR(64) NOT NULL DEFAULT '',
    cname VARCHAR(255) NOT NULL DEFAULT '',
    type VARCHAR(64) NOT NULL DEFAULT 'home',
    number BIGINT NOT NULL DEFAULT 0,
    FOREIGN KEY (login, cname) REFERENCES contacts (login, cname)
		ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (login, cname, number)
);

CREATE TABLE locations (
    login VARCHAR(64) NOT NULL DEFAULT '',
    cname VARCHAR(255) NOT NULL DEFAULT '',
    type VARCHAR(64) NOT NULL DEFAULT 'home',
    address VARCHAR(255) NOT NULL DEFAULT '',
    FOREIGN KEY (login, cname) REFERENCES contacts (login, cname)
		ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (login, cname, address)
);