1

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;
0

Even though Mysql InnoDB allows to reference non-unique column or set of columns, it's very bad practice, and not recommended especially with CASCADE option :

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. (https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html)

The right way is to have a reference to USER.ID , not to USER.NAME.

However, adding and index on USER.NAME will let you create a constraint you want :

CREATE INDEX IDX_NAME ON USER(NAME)

Your code would work as well if you removed size while creating index:

ALTER TABLE `USER`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `MAIL` (`MAIL`),
  ADD KEY `NAME_LINK` (`NAME`) USING BTREE;
   --NOT `NAME_LINK` (`NAME`(10))  !!
  ALTER TABLE CUSTOM_TRANSLATION add constraint fk1 foreign key (AUTHOR) references user(name); 
| improve this answer | |
  • If I'm understanding greatly your answer, i shouldn't do a foreign key constraint right here ? – Sayzeur Apr 13 '18 at 15:10
  • @Icesolider : The correct way is to have CUSTOM_TRANSLATION.author_id smallint instead of CUSTOM_TRANSLATION.author , and foreign key like ALTER TABLE CUSTOM_TRANSLATION add constraint fk1 foreign key (author_id) REFERENCES user(id); – a1ex07 Apr 13 '18 at 15:12
  • Oh, I see, thank you very much mate, I'm going to set this up, it's a clever option that I hadn't thinked of ! Have a great day :) – Sayzeur Apr 13 '18 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.