Page 1 of 1

Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 3:16 pm
by karengsh
Hi expert,

Thanks to Villeroy, I have now ammened my tables and added in a 'link table' - tutor_subject. Now, I receieved error on my link table - error code 1215.

Hope someone can tell me what's wrong:

Database : MySQL

Code: Select all   Expand viewCollapse view
Create table tutor(
id integer, auto_increment;
tutorName varchar(50), not null,
tutorNRIC varchar(50), not null, (Primary Key)
tutorEmail varchar(50), not null,
);



Code: Select all   Expand viewCollapse view
Create table subject(

id integer not null auto_increment,

subject varchar (50) not null,

subject_id int,

PRIMARY KEY  (subject_id),
KEY (id)
);


Code: Select all   Expand viewCollapse view
Create table tutor_subject(
tutorNRIC varchar(45),
subject_id int,
Primary Key(tutorNRIC, subject_id),
Foreign Key(tutorNRIC) references tutor(tutorNRIC),
Foreign Key(subject_id) references subject(subject_id));

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 3:50 pm
by Villeroy
This (like your first topic) has nothing to do with OpenOffice or any of its derivates. And no, I don't believe that you are running OpenOffice3 on Windows Vista. Any search engine gives you hundreds of valid answers for "mysql +error +1215"

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 4:02 pm
by karengsh
Hi Villeroy,

I am posting under external sources which is the rightful place right.
Yes, there are tons of 1215 error but found nothing similiar to my problem.
I hope you can tell me what's wrong. Tks.

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 4:19 pm
by RoryOF
A quick search threw up this


If you ever want to find out, why that error was , all you have to do is run below command and look for "LATEST FOREIGN KEY ERROR"

Command to run :-

mysql> SHOW ENGINE INNODB STATUS

You will know the reason for such errors.

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 4:25 pm
by karengsh
Hi RoryOF,

I already know what is the error which is in my title : cannot add foreign key constraint.

So, what is wrong with my script ?

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 4:38 pm
by Sliderule
karengsh wrote:So, what is wrong with my script ?

Since I do not have your database, it is just a guess. Please answer the following questions:

  1. What is the length of column tutorNRIC in table tutor ?
  2. What is the length of column tutorNRIC in table tutor_subject ?
  3. Are they the same?
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 5:42 pm
by Villeroy
Why do your tutors have an auto-ID that is not used as a primary key? If you really want to use a text field as PK, the auto-id is obsolete. I would use the auto-ID. If some more fields or combinations of fields are required to be unique you can build a unique index on that.

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 5:44 pm
by karengsh
Hi Sliderule,

It is the same. Varchar(45).

It does not solve the problem. :(

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 5:46 pm
by karengsh
Villeroy wrote:Why do your tutors have an auto-ID that is not used as a primary key? If you really want to use a text field as PK, the auto-id is obsolete. I would use the auto-ID. If some more fields or combinations of fields are required to be unique you can build a unique index on that.


Hi Villeroy,

tutorNRIC is a unique key (at least in my country) so it is much better to use this than the auto-ID.

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 5:55 pm
by Villeroy
Then remove the useless auto-ID. And adjust the length of the related foreign key. With HSQL it is not even possible to create auto-IDs that are not primary keys. The only purpose of these fields is to serve as a primary key.

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 6:18 pm
by karengsh
Hi Villeroy,

Is it ok to keep it as I would like to track the no of tutors enrolled ?

As mentioned, the length of the foreign key is the same as the primary key of tutor.

The problem is still there. How?

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 6:28 pm
by Villeroy
No, it isn't the same length.
Create table tutor(
...
tutorNRIC varchar(50), not null, (Primary Key)
...)

Create table tutor_subject(
tutorNRIC varchar(45),
...
Foreign Key(tutorNRIC) references tutor(tutorNRIC)

and you could at least try to omit the auto-key

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 6:35 pm
by karengsh
Hi Vileroy,

The attachment is abit inaccurate for the tutor table.

In my actual database, all I made the changes is to the link-table and the subject table.

Thus, the length is the same.

Thus, the problem is still there :(

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Mon Jun 05, 2017 6:54 pm
by Villeroy
And you've got to solve it on your own.

Re: Error Code: 1215. Cannot add foreign key constraint

PostPosted: Tue Jun 06, 2017 4:08 am
by karengsh
Alright. I will. Guess I just have to take time to find out about things.