-
Notifications
You must be signed in to change notification settings - Fork 0
/
debt.sql
67 lines (58 loc) · 1.83 KB
/
debt.sql
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
DROP DATABASE debtDB;
CREATE DATABASE IF NOT EXISTS debtDB;
GRANT ALL PRIVILEGES ON debtDB.* to 'debt'@'localhost'
identified by 'password';
USE debtDB;
CREATE TABLE user_list (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,/*Auto increment here? also primary key?*/
username VARCHAR(50),
password VARCHAR(20),
total_debt INT(100)
);
CREATE TABLE friend_list (
friend_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
friend_user VARCHAR(50),
friend_totalD INT(100)
);
CREATE TABLE friends (
user_id INT NOT NULL,
CONSTRAINT user_list_user_id_fk
FOREIGN KEY (user_id)
REFERENCES user_list (id),
friend_id INT NOT NULL,
CONSTRAINT friend_list_friend_id_fk
FOREIGN KEY (friend_id)
REFERENCES friend_list (friend_id)
);
CREATE TABLE user_debt (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, /*primary key*/
transaction VARCHAR(50),
debt_amount INT(100),
description VARCHAR(100)
);
CREATE TABLE debt_info (
user_id2 INT NOT NULL,
CONSTRAINT user_list_user_id2_fk
FOREIGN KEY (user_id2)
REFERENCES user_list (id),
debt_id INT NOT NULL,
CONSTRAINT user_debt_debt_id_fk
FOREIGN KEY (debt_id)
REFERENCES user_debt (id)
);
/*CREATE TABLE user_debt (
user_id INT NOT NULL,
CONSTRAINT friend_list_user_id_fk
FOREIGN KEY (user_id)
REFERENCES friend_list (user_id)
transaction VARCHAR(50),
debt_amount INT(100),
description VARCHAR(100)
);*/
INSERT INTO user_list VALUES (id,'Brennan Miller','peanut', 10);
INSERT INTO user_list VALUES ( id,'Austin Bouchard','12345', 30);
INSERT INTO user_list VALUES (id,'Riley Starrs','Riotpls01', 50);
/*INSERT INTO friend_debt VALUES('Zacharski', 55, 'stole my girlfriend');
INSERT INTO friend_debt VALUES('Benshoff', 10, 'Back massage');
INSERT INTO friend_debt VALUES('Patrick', -10, 'let me eat girlscout cookies');
INSERT INTO user_debt VALUES ('Raz', 1, 'Bummed a smoke');*/