MySQL:ERROR 1215: Cannot add foreign key constraint

Question

I've been trying to follow a tutorial that I've came across, which is about spring security. In some place, I need to create 2 tables in my database which is user and authorities. While doing this, I am using this script that is suggested like in the tutorial. http://docs.spring.io/spring-security/site/docs/3.0.x/reference/appendix-schema.html

I've already a user table in my db, so I just need to add autohirites table. Since I'm using MySQL, I've changed that query like below:

create table authorities (
      username varchar(70) not null,
      authority varchar(50) not null,
      CONSTRAINT fk_authorities_users foreign key(username) references user(userFirstName));
      create unique index ix_auth_username on authorities (username,authority);

Also, here is my user table too:

CREATE TABLE `user` (
    `userId` INT(11) NOT NULL AUTO_INCREMENT,
    `userFirstName` VARCHAR(70) NOT NULL,
    `userLastName` VARCHAR(70) NOT NULL,
    `userEmail` VARCHAR(70) NOT NULL,
    `userAddress` VARCHAR(500) NULL DEFAULT NULL,
    `userPhoneNumber` INT(13) NULL DEFAULT NULL,
    `isActive` BINARY(50) NULL DEFAULT '1\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
    `userPassword` VARCHAR(50) NOT NULL,
    `userConfirmPassword` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`userId`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2
;

When I try to run my first query which is going to create authorities table, I am getting ERROR 1215: Cannot add foreign key constraint error.

So far, I've been looked into these questions below, but none of them answered my problem + I think they are both the same questions:

MySQL Cannot Add Foreign Key Constraint

MySQL Error 1215: Cannot add foreign key constraint

Are both tables set to InnoDB? – Wouter Dec 11 '16 at 15:09 — Wouter, Dec 11 '16 at 15:09
Make username primary key in authorities table. – jophab Dec 11 '16 at 15:13 — jophab, Dec 11 '16 at 15:13

score 1 · Accepted Answer · answered Dec 11 '16 at 15:15

1

Try making userFirstName column unique.

answered Dec 11 '16 at 15:15

ag0702

381
1
6
18

score 1 · Answer 2 · answered Dec 11 '16 at 15:16

You haven't mentioned what the engine is for authorities, your user table does not have a unique index on userFirstName that would mean authoritiest would need to be INNODB. because:

InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

But I don't recommend this at all, it's always best to have a foreign key referencing a PRIMARY KEY, where that's not possible another unique key.

What you should really do is change your table as follows:

CREATE TABLE authorities (
      userid INT(11) not null,
      authority varchar(50) not null,
      CONSTRAINT fk_authorities_users foreign key(username) references user(userid));
      create unique index ix_auth_username on authorities (username,authority));

By doing so you are referencing a PRIMARY KEY but more importantly reducing a great deal of redundancy from your tables. There is absolutely no point in having the same ~70 character field repeated in both tables.

Thanks for the warning. A similar answer was given 1 min ago before yours. But I'll consider your "using id instead of name" in my code. Thanks! — Prometheus, Dec 11 '16 at 15:21
I could have made an answer well before that if I hadn't bothered to give an explaination :-) — e4c5, Dec 11 '16 at 15:23
ou shouldn't just consider that userId thing you should implement it because if you don't your databse isn't normalized. — e4c5, Dec 11 '16 at 15:24

MySQL:ERROR 1215: Cannot add foreign key constraint

2 Answers2