I am not able to create foreign key in mysql Error 150. Please help

i am trying to create a foreign key in my table. But when i executes my query it shows me error 150.

Error Code : 1025
Error on create foreign key of '.\vts\#sql-6ec_1' to '.\vts\tblguardian' (errno: 150)
(0 ms taken)

My Queries are

Query to create a foreign Key

alter table `vts`.`tblguardian` add constraint `FK_tblguardian` FOREIGN KEY (`GuardianPickPointId`) REFERENCES `tblpickpoint` (`PickPointId`)

Primary Key table

CREATE TABLE `tblpickpoint` (                                                           
                `PickPointId` int(4) NOT NULL auto_increment,                                         
                `PickPointName` varchar(500) default NULL,                                            
                `PickPointLabel` varchar(500) default NULL,                                           
                `PickPointLatLong` varchar(100) NOT NULL,                                             
                PRIMARY KEY  (`PickPointId`)                                                          
              ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC  

Foreign Key Table

CREATE TABLE `tblguardian` (                           
               `GuardianId` int(4) NOT NULL auto_increment,         
               `GuardianName` varchar(500) default NULL,            
               `GuardianAddress` varchar(500) default NULL,         
               `GuardianMobilePrimary` varchar(15) NOT NULL,        
               `GuardianMobileSecondary` varchar(15) default NULL,  
               `GuardianPickPointId` int(4) default NULL,     
               PRIMARY KEY  (`GuardianId`)                          
             ) ENGINE=InnoDB DEFAULT CHARSET=latin1    

Answers


Your problem is the type of the columns in your constraint are different. They must be the same.

`PickPointId` int(4) NOT NULL auto_increment, 

`GuardianPickPointId` varchar(100) default NULL,

For more information see the documentation:

Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.


Have a look at this step by step issue list

MySQL Error Number 1005 Can’t create table ‘.\mydb#sql-328_45.frm’ (errno: 150)


Need Your Help

Export orders from Woocommerce in JSON FORMAT

wordpress woocommerce json orders

I'm trying to export orders item from the plugin of wordpress Woocommerce and I create a template in wordpress with my querys for get the data of orders inside of array