MySQL Error 1215: Cannot add foreign key constrain

Question

I like to add foreign keys, this table, but not work! If I try new tables not work, but other, old tables it works. What's the problem?

CREATE TABLE `tanora` (
`idtanora` int(11) NOT NULL,
`tema` varchar(250) NULL,
`megjegyzes` varchar(255) DEFAULT NULL,
`datum` date NOT NULL,
`osztaly` varchar(20) NOT NULL,
`megtartott` int(11) NOT NULL,
`targy` varchar(45) NOT NULL,
`kezdete` time NOT NULL,
`vege` time NOT NULL,
PRIMARY KEY (`idtanora`),
UNIQUE KEY `idtanora_UNIQUE` (`idtanora`),
KEY `osztazon_idx` (`osztaly`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci;



CREATE TABLE `osztaly` (
`osztalyazon` varchar(20) NOT NULL,
`osztalyfonokazon` varchar(11) NOT NULL,
`osztalynev` varchar(45) NOT NULL,
`indul` year(4) NOT NULL,
PRIMARY KEY (`osztalyazon`),
UNIQUE KEY `osztalyazon_UNIQUE` (`osztalyazon`),
KEY `ofazon_idx` (`osztalyfonokazon`),
CONSTRAINT `tanarazon01` FOREIGN KEY (`osztalyfonokazon`) REFERENCES `tanar` (`szemelyiszam`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci;



ERROR 1215: Cannot add foreign key constraint 
SQL Statement:

ALTER TABLE `enaplo`.`tanora` 
ADD CONSTRAINT `osztalyra`
FOREIGN KEY (`osztaly`)
REFERENCES `enaplo`.`osztaly` (`osztalyazon`)
ON DELETE NO ACTION
ON UPDATE NO ACTION


ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'tanora' already exists
SQL Statement:
CREATE TABLE `tanora` (
 `idtanora` int(11) NOT NULL,
 `tema` varchar(250) NOT NULL,
 `megjegyzes` varchar(255) DEFAULT NULL,
 `datum` date NOT NULL,
 `osztaly` varchar(20) NOT NULL,
 `megtartott` int(11) NOT NULL,
 `targy` varchar(45) NOT NULL,
 `kezdete` time NOT NULL,
 `vege` time NOT NULL,
PRIMARY KEY (`idtanora`),
UNIQUE KEY `idtanora_UNIQUE` (`idtanora`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci



foreign key (osztaly)
references enaplo.osztaly (osztalyazon)
on delete no action
on update no action:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Answer 1

Make sure that you have consistent column definitions:

In your referencing table osztaly the foreign key column osztalyfonokazon needs to be defined with the exact same characteristics as applied to the referenced field in the tanar table.

Currently, osztalyfonokazon in osztaly is defined as follows:

`osztalyfonokazon` VARCHAR(11) NOT NULL

So, then in the tanar table the szemelyiszam definition should look like either

`szemelyiszam` VARCHAR(11) PRIMARY KEY

or

`szemelyiszam` VARCHAR(11) UNIQUE KEY NOT NULL

Depends on your needs.

Either way, ensure that szemelyiszam is a PRIMARY KEY or at least a UNIQUE KEY that is declared NOT NULL in the referenced tanar table.

Additionally, MySQL requires that the referenced columns be indexed for performance reasons. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.

^Source