Build A Menu For Bytes Of China

Get Help SQL
#1

Hello,

I have some issues getting on with the Build A Menu For Bytes Of China project - https://www.codecademy.com/paths/design-databases-with-postgresql/tracks/how-do-i-make-and-populate-my-own-database/modules/designing-a-database-schema/projects/restaurant-database-project-with-postgresql.

Specifically my issue is with step 8 and creating the many-to-many table categories_dishes.

My code to create the tables


create table restaurant (
  id integer PRIMARY KEY,
  name varchar(20),
  description varchar(100),
  rating decimal,
  telephone char(10),
  hours varchar(100)
);

create table address(
  id integer PRIMARY KEY,
  street_number varchar(10),
  street_name varchar(20),
  city varchar(20),
  state varchar(15),
  google_map_link varchar(50),
  restaurant_id integer REFERENCES restaurant(id)
);

create table category (
  id char(2) PRIMARY KEY,
  name varchar(20),
  description varchar(200)
);

create table dish (
  id integer PRIMARY KEY,
  name varchar(50),
  description varchar(200),
  hot_and_spicy boolean
);

create table review (
  id integer PRIMARY KEY,
  rating decimal,
  description varchar(100),
  date date,
  restaurant_id integer REFERENCES restaurant(id)
);

create table categories_dishes (
  category_id REFERENCES category(id),
  dish_id REFERENCES dish(id),
  price money,
  PRIMARY KEY (category_id, dish_id)
);

the values to insert

INSERT INTO categories_dishes VALUES (
  'C',
  1,
  6.95
);

INSERT INTO categories_dishes VALUES (
  'C',
  3,
  6.95
);

INSERT INTO categories_dishes VALUES (
  'LS',
  1,
  8.95
);

INSERT INTO categories_dishes VALUES (
  'LS',
  4,
  8.95
);

INSERT INTO categories_dishes VALUES (
  'LS',
  5,
  8.95
);

INSERT INTO categories_dishes VALUES (
  'HS',
  6,
  15.95
);

INSERT INTO categories_dishes VALUES (
  'HS',
  7,
  16.95
);

INSERT INTO categories_dishes VALUES (
  'HS',
  8,
  17.95
);

and the error message i’m getting

ERROR: syntax error at or near “REFERENCES”
LINE 2: category_id REFERENCES category(id),

any suggestions into what i’m doing wrong is greatly appreciated - also please let me know if i let out any important information!

#2

I’ll take a stab at this (I’ve not yet done this course). I think this line above is the issue. I think you have to add the FOREIGN KEY constraint in order for sql to not insert a foreign key that references a non-existent primary key. Something like:
FOREIGN KEY(restaurant_id) REFERENCES restaurant(id) );

See: https://www.postgresql.org/docs/9.3/ddl-constraints.html

Maybe try that and see what happens(?)

#3

Thanks for the push in the right direction.

i needed to add data types

create table categories_dishes (
  category_id char(2) REFERENCES category(id),
  dish_id integer REFERENCES dish(id),
  price money,
  PRIMARY KEY (category_id, dish_id)
);
1 Like
#4

can anyone tell me what’s wrong with my code? I get ERROR at step 4 of the project, when i create table ‘dishes’ but I don’t understand the error message.

create table food_categories (
  cat_id    varchar(2) primary key,
  cat_name  text,
  cat_des   text
);

create table dishes (
  dish_cat  varchar(2) references food_categories(cat_code) UNIQUE,
  dish_name text primary key,
  price     decimal(4,2),
  dish_des  text,
  spicy     boolean,
);

ERROR: syntax error at or near “)”
LINE 7: );

#5

Hello @kertesz41 ,

Can you confirm that the lesson you are having trouble with is the one included at the beginning of this post?

Step 4 is asking you to create a table name dish. When I get a syntax error like that, I usually look for missing or extra ``brackets, commas, semi-colons``` followed by misspelled key words. (In this case, make sure you don’t have any extra commas followed by your last column name.)