Resource-intensive process and defining complicated inter-relationships between It’s worthwhile noting that performing foreign key checks is a Usually results in a stronger, better-integrated database. Mysql> ALTER TABLE zoo ADD FOREIGN KEY (FK_species) REFERENCES species (id) Īs the examples above illustrate, foreign key relationshipsĬan play an important role in catching data entry errors, and implementing them To add a foreign key to an existing table, use the ALTER TABLE command with an ADD FOREIGN KEY clause to define the appropriate Mysql> ALTER TABLE zoo DROP FOREIGN KEY zoo_ibfk_1 `FK_species` tinyint(4) NOT NULL default ‘0’,ĬONSTRAINT ` zoo_ibfk_1` FOREIGN KEY (`FK_species`)Ĭommand with the DROP FOREIGN KEY clause, as To delete a foreign key relationship, first use the SHOWĬommand to find out InnoDB’s internal label for the Here, MySQL checks the species table to see if the species codeĮxists and, finding that it does, permits the record to be saved to the zoo table. Mysql > INSERT INTO zoo VALUES (1, ‘Harry’, 3) Happens when you enter the same record with a valid species code (one that Here, MySQL checks the speciestable to see if the species code existsĪnd, finding that it does not, rejects the record. Mysql> INSERT INTO zoo VALUES (1, ‘Harry’, 5) ĮRROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails You attempt to enter a record for Harry Hippopotamus with an invalid species Permitted only if the corresponding zoo.species field matchesĬlearly visible in the following output, which demonstrates what happens when Important: For non-InnoDB tables, the FOREIGN KEY clause is ignored.Īs the above illustrates, a foreign key relationship nowĮxists between the fields zoo.species and species.id. Mysql> CREATE TABLE zoo (id INT(4) NOT NULL, name VARCHAR(50) NOT NULL, FK_species TINYINT(4) NOT NULL, INDEX (FK_species), FOREIGN KEY (FK_species) REFERENCES species (id), PRIMARY KEY(id)) ENGINE=INNODB Mysql> INSERT INTO species VALUES (1, ‘orangutan’), (2, ‘elephant’), (3, ‘hippopotamus’), (4, ‘yak’) Mysql> CREATE TABLE species (id TINYINT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY(id)) ENGINE=INNODB Tables by the species code, so that only those entries in the zoo table which have a valid species codeĪccepted and saved to the database. Other listing animals in a zoo (table name: zoo). Begin by creating two tables ( ListingĪ), one listing animal species and their corresponding codes (table name: species) and the The best way to understand how this works is with anĮxample. Relationship must be similar in data type. Both tables must be of the InnoDB table type.MySQL tables, three conditions must be met: In order to set up a foreign key relationship between two Referential integrity within the tables that make up a database has become Keys through the new InnoDB table engine. This feature, recent versions of MySQL have implemented support for foreign However, given the high volume of user interest in For a long time, the popular open-source RDBMS MySQLĭid not support foreign keys, citing concerns that such support would erode Referential integrity is usually implemented through the use Producing a more accurate (and useful) database. Which does not already exist will fail this helps prevent user errors, When referential integrity exists, any attempt to link to a record Valid and no invalid links exist between the various tables that make up the The term refers to a state when all the references in a database are Referential integrity is an important concept in databaseĭesign. Recent versions of MySQL have implemented support for foreign keys through the new InnoDB table engine. An introduction to foreign keys and referential integrity in MySQL
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |