CODEWITHSUNDEEP
mysqlsqlsyntaxforeign-keysprimary-key
pjmil
pjmil

Reputation: 2097

SQL create table primary key and foreign key syntax

I'm creating a MySQL database for homework, and running into syntax error #1005 in phpmyadmin. I think it has something to do with the foreign keys but if w3schools has is right my syntax should be good.

Here's the SQL statements;

create table if not exists customers
(
    id int not null auto_increment, 
    cust_gname varchar(20) not null, 
    cust_fname varchar(30) not null, 
    cust_street varchar(30) not null, 
    cust_suburb varchar(30) not null, 
    cust_state varchar(6) not null, 
    cust_postcode varchar(4) not null, 
    cust_email varchar(50) not null, 
    cust_phone varchar(12), 
    cust_mobile varchar(12), 
    cust_user_id int, 
    foreign key (cust_user_id) references users(id),
    primary key (id)
);

create table if not exists ingredients
(
    id int, 
    name varchar(30) not null,
    primary key (id)
);

create table if not exists recipes
(
    id int, 
    name varchar(30) not null, 
    recipes_menu_id int,
    foreign key (recipes_menu_id) references menus(id)
    image varchar(30),
    primary key (id)
);

create table if not exists ingredients_recipes
(
    id int, 
    ingredients_recipes_ingredient_id int,
    foreign key (ingredients_recipes_ingredient_id) references ingredients(id), 
    ingredients_recipes_recipe_id int,
    foreign key (ingredients_recipes_recipe_id) references recipes(id),
    primary key (id)
);

create table if not exists menus
(
    id int, 
    description varchar(30) not null, 
    menus_restaurant_id int, 
    foreign key (menus_restaurant_id) references restaurants(id),
    primary key (id)
);

create table if not exists restaurants
(
    id int, 
    name varchar(30) not null, 
    address1 varchar(30) not null, 
    address 2 varchar(30), 
    suburb varchar(30) not null, 
    state varchar(10) not null, 
    postcode varchar(4) not null,
    primary key (id)
);

create table if not exists customers_ingredients
(
    id int, 
    customers_ingredients_customer_id int,
    foreign key (customers_ingredients_customer_id) references customers(id), 
    customers_ingredients_ingredient_id int, 
    foreign key (customers_ingredients_ingredient_id) references ingredients(id),
    primary key (id)
);

create table if not exists users
(
    id int, 
    username varchar(40) not null, 
    password varchar(50) not null, 
    group_id int,
    created DATETIME, 
    modified DATETIME,
    primary key (id)
);

create table if not exists groups
(
    id int, 
    name varchar(10) not null, 
    created DATETIME, 
    modified DATETIME,
    primary key (id)
);

Upvotes: 3

Views: 35282

Answers (1)

user1864610
user1864610

Reputation:

If you're creating a table with a foreign key reference, the table to which it refers must already exist. You're creating a customers table at the start of the script which refers to the users table which isn't created until near the end. There are other examples in the script too.

You need either to create the tables in the right order, or use set foreign_key_checks = 0; at the top to disable this requirement. Make sure you set foreign_key_checks = 1 at the end once all your tables are created.

Note: there may be other syntax errors in your script - I haven't checked it all.

Upvotes: 4

Related Questions