CODEWITHSUNDEEP
mysqlsqlforeign-keysmysql-error-1005
Mahdi_Nine
Mahdi_Nine

Reputation: 14761

error in adding foreign key

I have two tables: tab1,tab2

I want add a foreign key to tab2 and wrote this query

ALTER TABLE tab2
ADD FOREIGN KEY(name) REFERENCES tab1(name)

But I get this error:

error 1005(HY000):can't create table 'club.#sql-6f0_2' (errno:150)

What is wrong?

Edit

i write this tables only for test.

tab1

name char(20) private key

lname char(20)


tab2

ssn int private key

name char(20)

Upvotes: 0

Views: 221

Answers (3)

Ike Walker
Ike Walker

Reputation: 65587

I answered this for you about an hour ago in a comment on your other question, but here it is again:

You can get details on that error by running SHOW ENGINE INNODB STATUS\G and reading the LATEST FOREIGN KEY ERROR section.

The most likely reasons for this failure:

  • tab1.name and tab2.name are not exactly the same data type
  • tab1.name is not unique

Upvotes: 4

AbiusX
AbiusX

Reputation: 2404

You can not define Foreign Keys on default MySQL storage engines (ISAM, MyISAM), use InnoDB as engine, then add foreign key constraints.

Upvotes: 0

Piotr Salaciak
Piotr Salaciak

Reputation: 1663

try this:

alter table tab2
add foreign key my_key(name) references tab1(name)

maybe Your key should have a name?

Upvotes: 0

Related Questions