WK6Spxw9

1DROP TABLE IF EXISTS users, posts, tags, posts_tags, comments;
2
3CREATE TABLE users (
4  id serial PRIMARY KEY,
5  first_name VARCHAR(255),
6  last_name VARCHAR(255),
7  email text NOT NULL,
8  screen_name text NOT NULL
9);
10ALTER SEQUENCE users_id_seq RESTART 10001;
11
12CREATE TABLE posts (
13  id serial PRIMARY KEY,
14  title VARCHAR(255),
15  content text,
16  user_id int REFERENCES users(id) ON DELETE RESTRICT,
17  post_date timestamp DEFAULT now()
18);
19ALTER SEQUENCE posts_id_seq RESTART 101;
20
21CREATE TABLE tags (
22  id serial PRIMARY KEY,
23  tag_name text NOT NULL
24);
25ALTER SEQUENCE tags_id_seq RESTART 101;
26
27CREATE TABLE posts_tags (
28  post_id int REFERENCES posts(id) ON DELETE CASCADE,
29  tag_id int REFERENCES tags(id) ON DELETE RESTRICT,
30  PRIMARY KEY (post_id, tag_id)
31);
32
33CREATE TABLE comments (
34  id serial PRIMARY KEY,
35  comment_text text,
36  user_id int REFERENCES users(id) ON DELETE CASCADE NOT NULL,
37  post_id int REFERENCES posts(id) ON DELETE CASCADE NOT NULL
38);
39ALTER SEQUENCE comments_id_seq RESTART 1001;
40
41INSERT INTO users (email, screen_name)
42  VALUES ('mckoy@myownemail.yea', 'wolf'),
43         ('drinking@maitais.net', 'mclush'),
44         ('smoothing@jazz.org', 'mcjazz');
45
46INSERT INTO tags (tag_name)
47  VALUES ('#chilling'), ('#2drinks'), ('#canUdigit!'), ('#nosoupforU');
48
49INSERT INTO posts (title, content, user_id)
50  VALUES ('How I Became A Teacher Without A Degree...And Why I Left It', 
51          'Content coming soon.', 10001),
52         ('The Hard Life of an Engineer', 'Content coming soon', 10001),
53         ('Who Says You Can''t Work...and Potentially Change Your Career', 
54          'Content coming soon', 10001);
55
56INSERT INTO posts_tags (post_id, tag_id)
57  VALUES (101, 103), (102, 102), (102, 103), (102, 104), 
58         (103, 101), (103, 102);
59
60INSERT INTO comments (comment_text, user_id, post_id)
61  VALUES ('can''t wait to hear this sh*t', 10002, 101),
62         ('how hard is it to be an engineer, didn''t read it yet...', 10003, 102),
63         ('I really don''t see how this was possible...#fullofsh*t', 10002, 103);