Error Code: 1215. Cannot add foreign key constraint

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
karengsh
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

Error Code: 1215. Cannot add foreign key constraint

Post 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));
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post 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"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
karengsh
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Post 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.
OpenOffice 3.1 on Windows Vista
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
karengsh
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Post 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 ?
OpenOffice 3.1 on Windows Vista
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

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

Post 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.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
karengsh
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Post by karengsh »

Hi Sliderule,

It is the same. Varchar(45).

It does not solve the problem. :(
OpenOffice 3.1 on Windows Vista
karengsh
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Post 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.
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
karengsh
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Post 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?
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
karengsh
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Post 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 :(
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

And you've got to solve it on your own.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
karengsh
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Post by karengsh »

Alright. I will. Guess I just have to take time to find out about things.
OpenOffice 3.1 on Windows Vista
Post Reply