CODEWITHSUNDEEP
mysqlsqldatabaseconstraints
Marshall Tigerus
Marshall Tigerus

Reputation: 3764

MYSQL foreign key problems

I'm trying to set a foreign key, and running into issues (and getting the very helpful 1064 "you did something wrong" error)

Code to create:

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

The only way I can get it to not give me that error is by removing the foreign key line. What am I doing wrong here?

Here is the code for the apType table, very simple one:

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

Upvotes: 1

Views: 1006

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95512

To enforce foreign key constraints, you need to use Innodb rather than MyISAM. MyISAM parses and then ignores foreign key constraints.

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.

You also need to declare either a primary key constraint or a unique constraint on the referenced column. (The "id" column here.)

...the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.

CREATE TABLE `apType`(
  `id` int AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET=latin1;

On MySQL, you're better off not trying to declare primary key constraints inline.

CREATE TABLE `airport`(
  `id` int AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `rwyCount` int,
  `lat` float(4),
  `lon` float(4),
  `type` int, 
  PRIMARY KEY (id),
  FOREIGN KEY (type) REFERENCES apType(id)
)ENGINE=INNODB DEFAULT CHARSET=latin1;

Upvotes: 2

Related Questions