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?
To see FKs of a table first select table from the object explorer, then go to Structure tab and then select Relation view. Please note that in different versions it might be in different locations. On the Relation view screen you will see all foreign keys defined for this table (as a foreign table).
When a referenced foreign key is deleted or updated, respectively, the columns of all rows referencing that key will be set to NULL . The column must allow NULL or this update will fail.
You don't have to configure a foreign key constraint on a column just because it refers to another column. You could instead configure two tables such that one refers to the other, but without any defined foreign key.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With