MySQL SQL创建表主键外键语法

SQL create table primary key and foreign key syntax

提问by pjmil

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.

我正在为作业创建一个 MySQL 数据库，并在 phpmyadmin 中遇到语法错误 #1005。我认为这与外键有关，但如果 w3schools 是正确的，我的语法应该很好。

Here's the SQL statements;

这是 SQL 语句；

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)
);

回答by

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

如果您正在创建一个带有外键引用的表，它所引用的表必须已经存在。您在customers脚本的开头创建了一个表，该表指的users是直到接近尾声才创建的表。脚本中还有其他示例。

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 = 1at the end once all your tables are created.

您需要以正确的顺序创建表，或者set foreign_key_checks = 0;在顶部使用以禁用此要求。set foreign_key_checks = 1创建完所有表后，请确保您在最后。

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

注意：您的脚本中可能存在其他语法错误 - 我还没有全部检查过。