Shulou(Shulou.com)05/31 Report--

This article will explain in detail how mysql uses foreign key. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Experiment:

/ / create a parent table and add an index

CREATE TABLE parent (id INT,msg VARCHAR, INDEX index_id (id))

/ / View the index

SHOW INDEX FROM parent

/ / create a child table (foreign key)

CREATE TABLE child (c_id INT,c_mgs VARCHAR), FOREIGN KEY (c_id) REFERENCES parent (id)

ON DELETE CASCADE ON UPDATE CASCADE)

/ / insert data

INSERT INTO parent VALUES (1 recorder aaaa')

INSERT INTO child VALUES (1 recorder aaaa')

-Note: if the main table referenced by the foreign key of the child table has no data, the insert data of the child table will report an error

INSERT INTO child VALUES (2 recorder aaaa')

Error code: 1452

Cannot add or update a child row: a foreign key constraint fails (`czb`.`child`, CONSTRAINT `child_ibfk_ 1` FOREIGN KEY (`c_ id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

/ / View insert data

SELECT * FROM parent

SELECT * FROM child

/ / View foreign key constraints

SELECT * FROM information_ schema.`Key _ COLUMN_ USAGE` WHERE table_name='child'

SELECT * FROM information_ schema.`REFERENTIAL _ CONSTRAINTS`

/ / Delete foreign key constraint

ALTER TABLE child DROP FOREIGN KEY child_ibfk_1

/ / add a foreign key constraint

ALTER TABLE child ADD FOREIGN KEY (c_id) REFERENCES parent (id)

ON DELETE CASCADE ON UPDATE CASCADE

This is the end of the article on "how mysql uses foreign key". I hope the above content can be helpful to you so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.