MYSQL Foreign Key Errno (150) Can't create table

Question

I just started learning MYSQL in college and I have an important assignment to do for my class. I have to create a small database and I can't seem to add a table with foreign keys because of the errno(150) Here's what I have.

create table Country
(CountryName varchar (50) not null,
Primary Key (CountryName));

create table InterestGroup
(IntrestgrpName varchar (30) not null,
Primary Key (IntrestgrpName));

create table Organisation
(OrgName varchar (50) not null,
OrgAddress varchar (30),
OrgTelNo.varchar (30),
Primary Key (OrgName));

create table Qualification
(QualName varchar (50) not null,
Primary Key (QualName));

create table Member
(MemberID varchar (15) not null,
MemberName varchar (30),
MemberAdd varchar (50) not null,
CountryName varchar (50) not null,
IntrestgrpName varchar (30) not null,
QualName varchar (50) not null,
OrgName varchar (50) not null,
Primary Key (MemberID),
Foreign Key (CountryName) References Country (CountryName),
Foreign Key (IntrestgrpName) References InterestGroup (InterestgrpName),
Foreign Key (QualName) References Qualification (Qualname),
Foreign Key (OrgName) References Organisation (OrgName));

I cant seem to get the Member table to be created, It gives this error, ERROR 1005 (HY000): Can't create table 'iicp.member' (errno: 150) Thanks in advance for the help, I really need to solve this

Are you using InnoDB or MyISAM for the engine type? I think only InnoDB supports foreign keys. If you don't own the server itself, you might have to ask the DBA for this information. — Lukos, Feb 11 '13 at 14:03

echo_Me · Accepted Answer · 2013-02-11T14:20:02.167

here the working query

create table Country
(CountryName varchar (50) not null,
Primary Key (CountryName));

create table InterestGroup
(IntrestgrpName varchar (30) not null,
Primary Key (IntrestgrpName));

create table Organisation
(OrgName varchar (50) not null,
OrgAddress varchar (30),
OrgTelNo varchar (30),
Primary Key (OrgName));

create table Qualification
(QualName varchar (50) not null,
Primary Key (QualName));

create table Member
(MemberID varchar (15) not null ,
MemberName varchar (30),
MemberAdd varchar (50) not null,
CountryName varchar (50) not null,
IntrestgrpName varchar (30) not null,
QualName varchar (50) not null,
OrgName varchar (50) not null,
Primary Key (MemberID), 
Foreign Key (CountryName) References Country (CountryName),
Foreign Key (IntrestgrpName) References InterestGroup (IntrestgrpName),
Foreign Key (QualName) References Qualification (Qualname),
Foreign Key (OrgName) References Organisation (OrgName));

DEMO HERE SQLFIDDLE

nice that it worked for you! , make sure to accept the answer , to help others as helpful answer. — echo_Me, Feb 11 '13 at 16:54

score 1 · Answer 2 · answered Feb 11 '13 at 14:06

1

You SQL is correct. It worked for me with change the following change:

OrgTelNo.varchar (30) to OrgTelNo varchar (30)

answered Feb 11 '13 at 14:06

Ravi Maggon

83
8

Thanks, didnt know that the period would have caused the table to not be entered, But wouldn't an error like this only affect the input of Organisation table though? – KidDamien Kevon Daniel Feb 11 '13 at 15:53

score 0 · Answer 3 · answered Feb 11 '13 at 14:10

SHOW ENGINE INNODB STATUS;


...

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130211 15:09:26 Error in foreign key constraint of table test/member:
Foreign Key (IntrestgrpName) References InterestGroup (InterestgrpName),
Foreign Key (QualName) References Qualification (Qualname),
Foreign Key (OrgName) References Organisation (OrgName)):
Cannot resolve column name close to:
),
Foreign Key (QualName) References Qualification (Qualname),
Foreign Key (OrgName) References Organisation (OrgName))

...

You referred column named InterestgrpName in table InterestGroup but actual name of column is IntrestgrpName

Foreign Key (IntrestgrpName) References InterestGroup (InterestgrpName),

You have type error here -----------------------------------^

score 0 · Answer 4 · answered Feb 11 '13 at 14:16

0

OrgTelNo.varchar (30),

Instead of this you should write

OrgTelNo varchar (30),

And also it is a spell mistake in creation of last table member.

FOREIGN KEY ( IntrestgrpName ) REFERENCES InterestGroup ( IntrestgrpName)

Try this out instead.

answered Feb 11 '13 at 14:16

kewal

111
7

MYSQL Foreign Key Errno (150) Can't create table

4 Answers4