Disappearing Foreign Keys in phpMyAdmin

6
votes

I am creating a new table inside mysql and I am trying to add a foreign key constraint to one of the fields.

CREATE TABLE `onlineorder` (
  `receiptid` varchar(10) NOT NULL default '',
  `delivereddate` date default NULL,
  `cid` int(10) NOT NULL,
  `card#` int(10) default NULL,
  `expire` date default NULL,
  PRIMARY KEY  (`receiptid`),
  FOREIGN KEY (receiptid) REFERENCES purchase
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

However, after it creates it, I go into phpMyAdmin and export the table. and it seems like the foreign key constraint has disappeared.

CREATE TABLE `onlineorder` (
  `receiptid` varchar(10) NOT NULL default '',
  `delivereddate` date default NULL,
  `cid` int(10) NOT NULL,
  `card#` int(10) default NULL,
  `expire` date default NULL,
  PRIMARY KEY  (`receiptid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Does phpMyAdmin get rid of foreign keys or am I doing something wrong here?

4
mysqlforeign-keysphpmyadmin

4 Answers

8
votes

You need to use the InnoDB engine to have foreign keys.

Ref: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

6
votes

When you define a table with the MyISAM storage engine, it accepts FOREIGN KEY constraints, but it silently throws them away. It parses them, but does not store the constraint in the table metadata, and subsequently cannot enforce the constraint. When you ask to look at the database's idea of the table definition, it knows nothing about that constraint, as you have found out.

The same thing happens with CHECK constraints (regardless of the storage engine); it parses the syntax and accepts it, but then ignores it.

IMHO, this is a terrible thing for the MySQL product to do. It accepts standard SQL with no error, leaving you with the impression that it's going to support the constraint in the standard way. But it doesn't! Not even SHOW WARNINGS reveals that MySQL has disregarded the constraint.

If you use the InnoDB storage engine, it does heed the foreign key constraint.

1
votes

Also, PHPMyAdmin removes extracts any CONSTRAINTS until after all the tables have been created and the data has been inserted. If you use as PHPMySql to dump the table for you, you'll find at the end of the file an UPDATE table section with all your ADD CONSTRAINT entries.

0
votes

There are two type of constraints when you managing your tables with phpmyadmin:

  • internal: when you set constraints with phpmyadmin designer for example the constraints stored as internal,that will not be included in export.
  • innoDB: these constraints included in export check out linked video about it

[Video: Setting up a foreign key constraint][1]