Page 1 of 1
Error Code: 1215. Cannot add foreign key constraint
Posted: 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
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
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
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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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:
- What is the length of column tutorNRIC in table tutor ?
- What is the length of column tutorNRIC in table tutor_subject ?
- 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: Tue Jun 06, 2017 4:08 am
by karengsh
Alright. I will. Guess I just have to take time to find out about things.