mysql - 错误 1005(105) 无法创建表...但为什么呢？

Question

create table bankdb.customer(
    customer_name varchar(45) not null,
    social_security int not null,
    customer_street varchar(45),
    customer_city varchar(45),
primary key(`social_security`)
)engine=InnoDB;

create table bankdb.branch(
    branch_name varchar(45) not null,
    branch_city varchar(45),
    assets int,
primary key (`branch_name`)
)engine=InnoDB;

create table bankdb.account(
    branch_name varchar(45),
    account_number varchar(45) not null,
    balance int,
primary key (`account_number`),
constraint fk_acount_branch
    foreign key (`branch_name`)
    references bankdb.branch(`branch_name`)
)engine=InnoDB;  

create table bankdb.depositor(
    customer_name varchar(45) not null,
    account_number varchar(45) not null,
primary key (`customer_name`, `account_number`),
constraint fk_depositor_customer
    foreign key(`customer_name`)
    references bankdb.customer(`customer_name`),
constraint fk_depositor_account
    foreign key(`account_number`)
    references bankdb.account(`account_number`)
)engine=InnoDB;

这是我的sql代码...我收到错误无法为表bankdb.depositor创建表...我的外键有什么问题吗？有什么线索吗？

Answer 1

编辑，根据您的新脚本，以下内容似乎在 SQL Fiddle 中工作。我必须添加索引:

create table bankdb.customer(
    customer_name varchar(45) not null,
    social_security int not null,
    customer_street varchar(45),
    customer_city varchar(45),
  primary key(`social_security`),
  INDEX (customer_name)
)engine=InnoDB;

create table bankdb.branch(
    branch_name varchar(45) not null,
    branch_city varchar(45),
    assets int,
  primary key (`branch_name`)
)engine=InnoDB;

create table bankdb.account(
    branch_name varchar(45),
    account_number varchar(45) not null,
    balance int,
  primary key (`account_number`),
  INDEX (account_number),
  constraint fk_acount_branch
      foreign key (`branch_name`)
      references bankdb.branch(`branch_name`)
)engine=InnoDB;  

create table bankdb.depositor(
  customer_name varchar(45) not null,
  account_number varchar(45) not null,
  primary key (`customer_name`, `account_number`),
  INDEX (customer_name),  
  INDEX (account_number),
  constraint fk_depositor_customer
      foreign key(`customer_name`)
      references bankdb.customer(`customer_name`),
  constraint fk_depositor_account
      foreign key(`account_number`)
      references bankdb.account(`account_number`)
)engine=InnoDB;

参见SQL Fiddle with Demo

如果这是您的完整脚本，您的问题是为 account 创建表.

<罢工>

您正在尝试在 branch 上创建外键似乎不存在的表:

create table bankdb.account(
    branch_name varchar(45),
    account_number varchar(45) not null,
    balance int,
primary key (`account_number`),
constraint fk_acount_branch
    foreign key (`branch_name`)
    references bankdb.branch(`branch_name`)  -- does this exist
)engine=InnoDB;  

<罢工>