Some of the advantages of foreign key enforcement include: As we mentioned in the introduction, for storage engines other then Inno DB and the upcoming code-named “Falcon” storage engine, foreign keys are not natively supported.
My SQL in turn, parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.
The next step involves creating triggers for the NDB and My ISAM child tables which will ensure a corresponding parent_id value exists in the parent table when we insert a value into the child table.
CREATE TRIGGER insert_ndb_child BEFORE INSERT ON ndb_child FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM ndb_parent WHERE nparent_id=new.nparent_id)= 0 THEN INSERT error_msg VALUES ('Foreign Key Constraint Violated!
'); END IF; END; CREATE TRIGGER insert_myisam_child BEFORE INSERT ON myisam_child FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM myisam_parent WHERE mparent_id=new.mparent_id)=0 THEN INSERT error_msg VALUES ('Foreign Key Constraint Violated!
In this article we explore how to program foreign keys into a My SQL Server.
This is important when your application requires referential integrity and the storage engine you’d like to use, as in the case of My ISAM or NDB (Cluster), does not support this functionality natively.
We should note that Inno DB and the upcoming code-named “Falcon” storage engine, both support foreign keys.In general, a foreign key is a field within a database record that points to a key (or group of fields forming a key) of another record in a different table.In this arrangement, a foreign key in one table will typically refer to the primary key of another table.This enables references which can be made to link information together.This type of design is a major component of what is known as “database normalization”.It should also be noted, that data, which serves as a foreign key in one record, cannot be removed if there is another record that assumes its existence.