MySQL Foreign Key Constraint

Question

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?

There are articles all over the web that explain this. Have you tried using your favourite search engine? It's a very productive skill to learn - the ability to research your own answers to your own problems. — MatBailie
– MatBailie, Commented Jul 28, 2013 at 22:22
I did indeed, and many of the answers provided useful information, but nothing that was able to solve my problem — Marshall Tigerus
– Marshall Tigerus, Commented Jul 28, 2013 at 22:26

Community · Accepted Answer · 2017-05-23 12:20:21Z

2

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?

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jul 28, 2013 at 22:15

pylua

6355 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marshall Tigerus Over a year ago

I tried that and i get a #1005 - Can't create table 'todtm-db.airport' (errno: 150) error.

pylua Over a year ago

What you have to do, before you create the second table, is create an index. Create index ix on apType(type); . This is because you are not referencing the primary key :) Then, create table airport.

Marshall Tigerus Over a year ago

I completely forgot I had it referencing something other than the primary key. Fixed it, it works, and give the proper error. Thank you

Collectives™ on Stack Overflow

MySQL Foreign Key Constraint

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related