xx0qyihk

1/*
2This is the USERS table.
3ID not null, auto increments.
4USERNAME, PASSWORD, FNAME, LNAME, & EMAIL not null.
5FOLLOWERS, FOLLOWING, and TWEETS default set to 0.
6The primary key is ID.
7*/
8DROP TABLE IF EXISTS users;
9CREATE TABLE users (
10    id int NOT NULL AUTO_INCREMENT,
11    username varchar(15) NOT NULL,
12    password varchar(32) NOT NULL,
13	  fname varchar(25) NOT NULL,
14	  lname varchar(25) NOT NULL,
15	  email varchar(50) NOT NULL,
16    PRIMARY KEY (id));
17
18/*
19This is the FOLLOWS table.
20ID not null, auto increments.
21USER1_ID & USER2_ID not null.
22The primary key is ID.
23Foreign keys-- USER1_ID references ID from USERS table.
24               USER2_ID references ID from USERS table.
25*/
26DROP TABLE IF EXISTS follows;
27CREATE TABLE follows (
28    id int NOT NULL AUTO_INCREMENT,
29    user1_id int NOT NULL,
30    user2_id int NOT NULL,
31    PRIMARY KEY (id),
32	  FOREIGN KEY (user1_id) REFERENCES users (id),
33	  FOREIGN KEY (user2_id) REFERENCES users (id));
34
35/*
36This is the TWEETS table.
37ID not null, auto increments.
38USER_ID, TWEET, & TIMESTAMP not null.
39The primary key is ID.
40Foreign key-- USER_ID references ID from USERS table.
41*/
42DROP TABLE IF EXISTS tweets;
43CREATE TABLE tweets (
44    id int NOT NULL AUTO_INCREMENT,
45    user_id int NOT NULL,
46    tweet varchar(140) NOT NULL,
47    time_tweeted timestamp NOT NULL,
48    PRIMARY KEY (id),
49	  FOREIGN KEY (user_id) REFERENCES users (id));
50
51/*
52This is the LIKES table.
53ID not null, auto increments.
54USER_ID & TWEET_ID not null.
55The primary key is ID.
56Foreign keys-- USER_ID references ID from USERS table.
57               TWEET_ID references ID from TWEETS table.
58*/
59DROP TABLE IF EXISTS likes;
60CREATE TABLE likes (
61	id int NOT NULL AUTO_INCREMENT,
62	user_id int NOT NULL,
63	tweet_id int NOT NULL,
64	PRIMARY KEY (id),
65	FOREIGN KEY (user_id) REFERENCES users (id),
66	FOREIGN KEY (tweet_id) REFERENCES tweets (id));