8vUrGgsb

1drop table if exists ingredients cascade;
2drop table if exists recipes cascade;
3drop table if exists users cascade;
4drop table if exists recipe_ingredients cascade;
5drop table if exists ratings cascade;
6CREATE TABLE ingredients
7(
8id serial PRIMARY KEY,
9name varchar
10)
11;
12CREATE TABLE recipes
13(
14id serial PRIMARY KEY,
15name varchar NOT NULL,
16author_id integer NOT NULL,
17instructions varchar,
18UNIQUE (author_id, name)
19)
20;
21CREATE TABLE recipe_ingredients
22(
23recipe_id INTEGER  REFERENCES recipes(id),
24ingredient_id INTEGER REFERENCES ingredients (id),
25amount real,
26unit varchar,
27PRIMARY KEY (recipe_id, ingredient_id)
28)
29;
30CREATE TABLE users
31(
32id serial PRIMARY KEY,
33name varchar NOT NULL,
34age integer CHECK ((age is NULL) or (age>=0)),
35email varchar NOT NULL UNIQUE,
36favourite_recipe_id INTEGER REFERENCES recipes(id)
37)
38;
39CREATE TABLE ratings
40(
41recipe_id INTEGER REFERENCES recipes(id),
42user_id INTEGER REFERENCES users(id),
43rating INTEGER CHECK ((rating>=0) AND (rating <=10)) NOT NULL,
44PRIMARY KEY (recipe_id, user_id),
45UNIQUE (recipe_id, user_id)
46);
47
48ALTER TABLE recipes ADD
49FOREIGN KEY (author_id)
50REFERENCES users (id)