CODEWITHSUNDEEP
mysql
noob_coder
noob_coder

Reputation: 829

Foreign key constraint is incorrectly formed error in mysql

i have 2 tables table1 and table2 table1 has id and name and table2 has id,instance(foreign key fromtable1),hostname1 and hostname2 feilds.

creating table1

mysql> CREATE TABLE table1 (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(50)
    -> );

when i am trying to create table2 with foreign key it throws me the error

"ERROR 1005 (HY000): Can't create table `databasename`.`table2` (errno: 150 "Foreign key constraint is incorrectly formed")"

the code is

mysql> CREATE TABLE table2(
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> instance VARCHAR(50) NOT NULL,
    -> hostname1 VARCHAR(50) NOT NULL,
    -> hostname2 VARCHAR(50) NOT NULL,
    -> CONSTRAINT `fk_name`
    -> FOREIGN KEY(instance) REFERENCES table1(name)
    -> ON DELETE CASCADE
    -> ON UPDATE RESTRICT
    -> );

what am i doing wrong here?

Upvotes: 0

Views: 272

Answers (2)

Ahmed Marei
Ahmed Marei

Reputation: 11

FK should references PK or UNIQUE field.

you can make table2 (instance) to be same type as table1 primary key (INT) and reference to table1(id).

or if you must references name so table1(name) should be UNIQUE

Upvotes: 1

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

name VARCHAR(50) is nullable in table1 while it's not null in table2.

instance maps to name which is not null

 -> instance VARCHAR(50) NOT NULL,

Upvotes: 0

Related Questions