COP4331-Group14-ContactManager/DBsetup.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
-- Create database
CREATE DATABASE ContactManager;
USE ContactManager;
-- Create tables
CREATE TABLE Users (
    Login VARCHAR(64) NOT NULL DEFAULT '',
    Pass VARCHAR(64) NOT NULL DEFAULT '',
    PRIMARY KEY (Login)
);
CREATE TABLE Contacts (
    Login VARCHAR(64) NOT NULL DEFAULT '',
    Name VARCHAR(255) NOT NULL DEFAULT '',
    FOREIGN KEY (Login) REFERENCES Users (Login)
		ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (Login, Name)
);
CREATE TABLE Emails (
    Login VARCHAR(64) NOT NULL DEFAULT '',
    Name VARCHAR(255) NOT NULL DEFAULT '',
    Type VARCHAR(64) NOT NULL DEFAULT 'Home',
    Address VARCHAR(255) NOT NULL DEFAULT '',
    FOREIGN KEY (Login, Name) REFERENCES Contacts (Login, Name)
		ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (Login, Name, Address)
);
CREATE TABLE Phones (
    Login VARCHAR(64) NOT NULL DEFAULT '',
    Name VARCHAR(255) NOT NULL DEFAULT '',
    Type VARCHAR(64) NOT NULL DEFAULT 'Home',
    Number INT(10) NOT NULL DEFAULT 0,
    FOREIGN KEY (Login, Name) REFERENCES Contacts (Login, Name)
		ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (Login, Name, Number)
);
CREATE TABLE Locations (
    Login VARCHAR(64) NOT NULL DEFAULT '',
    Name VARCHAR(255) NOT NULL DEFAULT '',
    Type VARCHAR(64) NOT NULL DEFAULT 'Home',
    Address VARCHAR(255) NOT NULL DEFAULT '',
    FOREIGN KEY (Login, Name) REFERENCES Contacts (Login, Name)
		ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (Login, Name, Address)
);