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?
Best Answer
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