CODEWITHSUNDEEP
mysqlforeign-keys
Marshall Tigerus
Marshall Tigerus

Reputation: 3764

MySQL Foreign Key Constraint

I'm having some issues with a foreign key constraint, and am probably not putting the code together properly.

The idea is that the airports table will only accept a type value that is in the apType table. If the type is not in the apType table, it should generate an error. However, I have been testing this and I am unable to get it to generate an error, it just places the type entered into the database and is happy with it.

Airport Table:

 CREATE TABLE `airport`(
`id` int primary key AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`rwyCount` int,
`lat` float(6),
`lon` float(6),
`type` varchar(255), 
constraint FOREIGN KEY (type) REFERENCES apType(type) match simple
)ENGINE=MyISAM DEFAULT CHARSET=latin1;

apType Table:

CREATE TABLE `apType`(
`id` int primary key AUTO_INCREMENT,
`type` varchar(255) NOT NULL
 )ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert of values for apType (these are the only values that should be valid):

INSERT INTO `apType` (`type`) VALUES ('private'),('public'),('military');

Insert that should generate an error:

 insert into `airport` (`name` , `rwyCount` , `type` , `lat` , `lon`) values ('failland', 3 , 'space', 45.588611, -122.5975);

Can anyone figure out this issue?

Upvotes: 0

Views: 89

Answers (1)

pylua
pylua

Reputation: 635

It appears that foreign keys have not yet been implemented in the DB engine that you are using. Why doesn't MySQL's MyISAM engine support Foreign keys?

Maybe consider switching to InnoDb?

Upvotes: 2

Related Questions