CODEWITHSUNDEEP
mysqlsqlforeign-keys
George F-bot
George F-bot

Reputation: 11

Error 1005(105) can't create table... but why?

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;

That's my sql code... i get the error can't create table for the table bankdb.depositor... Is there anything wrong with my foreign keys? Any clues?

Upvotes: 0

Views: 339

Answers (1)

Taryn
Taryn

Reputation: 247710

Edit, based on your new script the following appears to be working in SQL Fiddle. I had to add indexes:

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;

See SQL Fiddle with Demo

If this is your full script your issue is with the create table for account.

You are trying to create a foreign key on the branch table which doesn't appear to exist:

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;

Upvotes: 2

Related Questions