sinesine sinesine - 1 month ago 14
MySQL Question

Error (1005) when trying to create foreign key

I'm trying to create a basic foreign constraint, however I'm getting a syntax error.

#1005 - Can't create table 'my_database'.'#sql-334f_952bc' (errno: 150 "Foreign key constraint is incorrectly formed")


I first create the tables and then use the 'alter table' method to create the foreign constraint.

Creating tables:

CREATE TABLE `tbl_flights` (
`flight_id` int(11) NOT NULL AUTO_INCREMENT
`aircraft_id` int(11) NOT NULL
`date` date NOT NULL
`auth_by` varchar(255) NOT NULL
`auth_duration` time NOT NULL
PRIMARY KEY (`flight_id`)
)
;

CREATE TABLE `tbl_aircraft` (
`aircraft_id` int(11) NOT NULL AUTO_INCREMENT
`registration` char(6) NOT NULL
`insurance` date NOT NULL
`awrc` date NOT NULL
PRIMARY KEY (`aircraft_id`)
)
;


Creating foreign key/constraint:

ALTER TABLE `tbl_aircraft`
ADD CONSTRAINT `fk_aircraft_id` FOREIGN KEY ( `aircraft_id` )
REFERENCES `my_database`.`tbl_flights` ( `aircraft_id` )
ON DELETE RESTRICT ON UPDATE CASCADE ;


If anyone could identify the issue here I would greatly appreciate it.

Answer

base in the error message could be you missed the ; at the end of the statement

CREATE TABLE `tbl_flights` (
  `flight_id`      int(11)       NOT NULL AUTO_INCREMENT
  `aircraft_id`    int(11)       NOT NULL
  `date`           date          NOT NULL
  `auth_by`        varchar(255)  NOT NULL
  `auth_duration`  time          NOT NULL
  PRIMARY KEY (`flight_id`)
)
; /* add this */

CREATE TABLE `tbl_aircraft` (
  `aircraft_id`    int(11) NOT NULL AUTO_INCREMENT
  `registration`   char(6) NOT NULL
  `insurance`      date    NOT NULL
  `awrc`           date    NOT NULL
  PRIMARY KEY (`aircraft_id`) 
)
; /* add this*/

could be you have inverted the tables

 ALTER TABLE `tbl_flights` 
 ADD CONSTRAINT  `fk_aircraft_id` FOREIGN KEY ( `aircraft_id` ) 
 REFERENCES `my_database`.`tbl_aircraft` ( `aircraft_id` ) 
 ON DELETE RESTRICT ON UPDATE CASCADE ;

or you must add a foreign column in TABLE tbl_aircraft