I'm currently building a database and adding my foreign keys constraint on phpMyAdmin but I'm struggling with one of them. Indeed, I've looked on the internet for many possible solutions but any of them was working for me.
The fact is that I'm trying to add a foreign key contraint in my CUSTOM_TRANSLATION table on the AUTHOR, referring to the
NAME attribute in my USER table with a
CASCADE effect ON UPDATE and ON DELETE.
Type matches and my indexes are set, I still get a #1215 error (cannot add foreign key constraint).
I didn't pasted the whole code because I don't think it's really usefull, just pasted the interesting part, I guess.
Thank you for your time and help, fellas :)
CREATE TABLE `USER` ( `ID` tinyint(3) UNSIGNED ZEROFILL NOT NULL, `NAME` varchar(20) NOT NULL, `MAIL` varchar(50) NOT NULL, `PASSWORD` varchar(150) NOT NULL, `RANK` enum('Admin','Translator','ViewerSDTV','ViewerCustomer','Customer','TranslatorRestricted') NOT NULL, `SOCIETY` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `CUSTOM_TRANSLATION` ( `ID` smallint(5) UNSIGNED ZEROFILL NOT NULL, `TRANSLATION_ID` smallint(5) UNSIGNED ZEROFILL NOT NULL, `MESSAGE_ID` smallint(5) UNSIGNED ZEROFILL NOT NULL, `TRANSLATED_MESSAGE` varchar(80) NOT NULL, `VERSION` decimal(3,2) UNSIGNED ZEROFILL NOT NULL, `AUTHOR` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `USER` ADD PRIMARY KEY (`ID`), ADD UNIQUE KEY `MAIL` (`MAIL`), ADD KEY `NAME_LINK` (`NAME`(10)) USING BTREE;