MySQL | Unable to create foreign key between two tables

Author - Webner
4.10.2016
|
0 Comments
|

We have two tables say table1 and table 2. In table 1, there is a field named Id and this field is set as primary key in table 1. We want to use it as foreign key in table 2.

Fields in both the tables:
Table 1
Fields
Indexes
Id
Primary Key
Name

Table 2
Fields
Indexes
Id
Primary Key
Name

Table1_ID
Foreign key references to table1(Id)

We used the following query to create table2:

CREATE TABLE table2
(
Id int,
Name varchar,
Table1_ID int,
PRIMARY KEY (Id),
CONSTRAINT fk_Pertable2 FOREIGN KEY (Table1_ID )
REFERENCES table1(Id)
)

But when we checked the structure of table 2, the foreign key was not created.

Then we tried Alter table query:

ALTER TABLE table2
ADD CONSTRAINT fk_Pertable2 FOREIGN KEY (Table1_ID )
REFERENCES table1(Id)

But still, the foreign key was not created in table 2.

Finally, we found that the reason of problem is that the Storage Engine of both the tables was different. To check the storage engine of all table of a database, execute the following query:

SHOW TABLE STATUS. You will find all the table with their corresponding information. In the result information, you will also find the storage engine.

The storage engine of Table1 was MRG_MYISAM and storage engine of Table2 was InnoDB.

We can change storage engine using query as well as using PHPMyAdmin.

Change storage engine of a table using the query:

ALTER TABLE table1 ENGINE = InnoDB;

Change storage engine of table using PHPMyAdmin:

Open PHPMyAdmin.
Open the table.

Select Operation from Menu Bar.You will get the screen like:

You can change storage engine from Table option as seen in the screenshot. You can check storage engine by the same process.

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Leave a Reply

Your email address will not be published. Required fields are marked *