Error Code: 1215. Cannot add foreign key constraint

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

Error Code: 1215. Cannot add foreign key constraint

Postby karengsh » Mon Jun 05, 2017 3:16 pm

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

Postby Villeroy » Mon Jun 05, 2017 3:50 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby karengsh » Mon Jun 05, 2017 4:02 pm

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
karengsh
 
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Postby RoryOF » Mon Jun 05, 2017 4:19 pm

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.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 28411
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby karengsh » Mon Jun 05, 2017 4:25 pm

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
karengsh
 
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Postby Sliderule » Mon Jun 05, 2017 4:38 pm

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
Sliderule
Volunteer
 
Posts: 1162
Joined: Thu Nov 29, 2007 9:46 am

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

Postby Villeroy » Mon Jun 05, 2017 5:42 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby karengsh » Mon Jun 05, 2017 5:44 pm

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

Postby karengsh » Mon Jun 05, 2017 5:46 pm

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
karengsh
 
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Postby Villeroy » Mon Jun 05, 2017 5:55 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby karengsh » Mon Jun 05, 2017 6:18 pm

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
karengsh
 
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Postby Villeroy » Mon Jun 05, 2017 6:28 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby karengsh » Mon Jun 05, 2017 6:35 pm

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
karengsh
 
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Postby Villeroy » Mon Jun 05, 2017 6:54 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby karengsh » Tue Jun 06, 2017 4:08 am

Alright. I will. Guess I just have to take time to find out about things.
OpenOffice 3.1 on Windows Vista
karengsh
 
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest