CODEWITHSUNDEEP
mysqlsqlcreate-table
JohnnnnnY
JohnnnnnY

Reputation: 35

MySQL #1005 can't create table, but don't says why, errno:150

I'm making a database, with several tables, and so far I could make the tables, and update it with some demo data, but when a tried to make a new table for connecting two of my tables I just get this can't create table error whatever I did. Tried to rename the entities to an entirely different name maybe that's the problem but it wasn't.

CREATE DATABASE IF NOT EXISTS ETR;

CREATE TABLE Hallgato (
OktAzonosito INT(6) PRIMARY KEY,
EHAazonosito VARCHAR(11),
TeljesNev VARCHAR(50),
Szemelyazonosito INT(6),
AnyaNyelv VARCHAR(20),
VegzettsegSzint VARCHAR(25),
AnyjaNeve VARCHAR(35),
SzuletesiHely VARCHAR(30),
SzuletesiEv DATE,
Allampolgarsag VARCHAR(30),
Neme VARCHAR(5),
Adoazonosito INT(6),
TAJszam INT(6),
BankszamlaSzam INT(9)
) ENGINE = InnoDB;

CREATE TABLE OktAdat (
OktAzonosito INT(6),
NyelvVizsgaNyelve VARCHAR(15),
NyVegzesDatuma DATE,
NyIntezmeny VARCHAR(35),
EgyebVegzetteseg VARCHAR(15),
EgyVegzesDatuma DATE,
ID INT(5) PRIMARY KEY NOT NULL auto_increment,
FOREIGN KEY (OktAzonosito) REFERENCES Hallgato(OktAzonosito) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE Oktato (
TEHAazonosito VARCHAR(11) PRIMARY KEY,
Nev VARCHAR(50),
SzuletesiEv DATE,
Szakterulet VARCHAR(25),
Telefonszam INT(9),
Email VARCHAR(50)
) ENGINE = InnoDB;

CREATE TABLE Kurzus (
KurzusKod VARCHAR(8) PRIMARY KEY,
KurzusNev VARCHAR(30),
Idotartam INT(3),
EloadasHelye VARCHAR(25),
Tipus CHAR(3),
Vizsgatipus VARCHAR(7),
KreditErtek INT(2),
OktatoKod VARCHAR(11)
) ENGINE = InnoDB;

CREATE TABLE Terem (
Sorszam INT(3) PRIMARY KEY NOT NULL,
Epuletszam INT(1),
Kapacitas INT(3),
IRszam INT(4),
Utca CHAR(25),
Hazszam INT(2)
) ENGINE = InnoDB;

CREATE TABLE DiakKurz (
kd_id INT (5) PRIMARY KEY NOT NULL auto_increment,
DKKod VARCHAR(8),
EHA VARCHAR(11),
FOREIGN KEY (EHA) REFERENCES Hallgato(EHAazonosito) ON DELETE CASCADE,
FOREIGN KEY (DKKod) REFERENCES Kurzus(KurzusKod) ON DELETE CASCADE
) ENGINE = InnoDB;

The problem is with creating the DiakKurz table.

Upvotes: 0

Views: 204

Answers (4)

udik
udik

Reputation: 357

it happened also to me after a dump of the whole schema, what helped was to wrap whole dump within disabled foreign key checks

SET foreign_key_checks=0;

...dump

SET foreign_key_checks=1;

Upvotes: 0

André Keller
André Keller

Reputation: 3199

You'll have to set an INDEX for column EHAazonosito in table Hallgato

CREATE TABLE Hallgato (
OktAzonosito INT(6) PRIMARY KEY,
EHAazonosito VARCHAR(11), INDEX (EHAazonosito),
TeljesNev VARCHAR(50),
Szemelyazonosito INT(6),
AnyaNyelv VARCHAR(20),
VegzettsegSzint VARCHAR(25),
AnyjaNeve VARCHAR(35),
SzuletesiHely VARCHAR(30),
SzuletesiEv DATE,
Allampolgarsag VARCHAR(30),
Neme VARCHAR(5),
Adoazonosito INT(6),
TAJszam INT(6),
BankszamlaSzam INT(9)
) ENGINE = InnoDB;

Upvotes: 1

user330315
user330315

Reputation:

This 

FOREIGN KEY (EHA) REFERENCES Hallgato(EHAazonosito) ON DELETE CASCADE,

doesn't work because EHAazonosito is not the primary key of the Hallgato table. A foreign key can only reference a primary (or unique) key.

Btw: are you awary that the 6 in int(6) is not about limiting the values in the column?

It doesn't do anything. int(6) is the same as int. The 6 is only there to give client applications a hint(!) with how many digits the column should be displayed. It does not enforce anything at all.

Upvotes: 1

Christopher Armstrong
Christopher Armstrong

Reputation: 7953

Have you tried looking up the error?

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. To display a detailed explanation of the most recent InnoDB foreign key error in the server, issue SHOW ENGINE INNODB STATUS.

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Upvotes: 0

Related Questions