Summary: in this tutorial, you will learn about MySQL foreign key and how to create, drop, and disable a foreign key constraint. Show
Introduction to MySQL foreign keyA foreign key is a column or group of columns in a table that links to a column or group of columns in another table. The foreign key places constraints on data in the related tables, which allows MySQL to maintain referential integrity. Let’s take a look at the following 5 and 6 tables from the sample database.  In this diagram, each customer can have zero or many orders and each order belongs to one customer. The relationship between 5 table and 6 table is one-to-many. And this relationship is established by the foreign key in the 6 table specified by the 0 column. The 0 column in the 6 table links to the 0 primary key column in the 5 table. The 5 table is called the parent table or referenced table, and the 6 table is known as the child table or referencing table. Typically, the foreign key columns of the child table often refer to the primary key columns of the parent table. A table can have more than one foreign key where each foreign key references to a primary key of the different parent tables. Once a foreign key constraint is in place, the foreign key columns from the child table must have the corresponding row in the parent key columns of the parent table or values in these foreign key column must be 7 (see the 8 action example below). For example, each row in the 6 table has a 0 that exists in the 0 column of the 5 table. Multiple rows in the 6 table can have the same 0. Self-referencing foreign keySometimes, the child and parent tables may refer to the same table. In this case, the foreign key references back to the primary key within the same table. See the following 5 table from the sample database. The 6 column is a foreign key that refers to the 7 column which is the primary key of the 5 table. This relationship allows the 5 table to store the reporting structure between employees and managers. Each employee reports to zero or one employee and an employee can have zero or many subordinates. The foreign key on the column 6 is known as a recursive or self-referencing foreign key. MySQL INSERT INTO products(productName, categoryId) VALUES('iPad',3);Code language: SQL (Structured Query Language) (sql)1 syntaxHere is the basic syntax of defining a foreign key constraint in the 2 or 3 statement:
In this syntax: First, specify the name of foreign key constraint that you want to create after the 4 keyword. If you omit the constraint name, MySQL automatically generates a name for the foreign key constraint. Second, specify a list of comma-separated foreign key columns after the 1 keywords. The foreign key name is also optional and is generated automatically if you skip it. Third, specify the parent table followed by a list of comma-separated columns to which the foreign key columns reference. Finally, specify how foreign key maintains the referential integrity between the child and parent tables by using the 6 and 7 clauses. The 8 determines action which MySQL will take when values in the parent key columns are deleted ( 6) or updated ( 7). MySQL has five reference options: 1, 8, 3, 4, and 5.
In fact, MySQL fully supports three actions: 4, 1 and 8. If you don’t specify the 6 and 7 clause, the default action is 4. MySQL INSERT INTO products(productName, categoryId) VALUES('iPad',3);Code language: SQL (Structured Query Language) (sql)1 examplesLet’s create a new database called 0 for the demonstration.
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)4 & Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)3 actionsInside the 0 database, create two tables 4 and 5:
The 6 in the 5 table is the foreign key column that refers to the 6 column in the 4 table. Because we don’t specify any 7 and 6 clauses, the default action is 4 for both update and delete operation. The following steps illustrate the 4 action. 1) Insert two rows into the 4 table:
2) Select data from the 4 table:
3) Insert a new row into the 5 table:
It works because the 6 1 exists in the 4 table. 4) Attempt to insert a new row into the 5 table with a 6 value does not exist in the 4 table:
MySQL issued the following error:
5) Update the value in the 6 column in the 4 table to 14:
MySQL issued this error:
Because of the 4 option, you cannot delete or update 16 since it is referenced by the 17 18 in the 5 table. Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)1 actionThese steps illustrate how 21 and 22 actions work. 1) Drop the 5 table: 0 2) Create the 5 table with the 21 and 26 options for the foreign key: 1 3) Insert four rows into the 5 table: 2 4) Select data from the 5 table: 3 5) Update 6 1 to 100 in the 4 table: 4 6) Verify the update:
7) Get data from the 5 table: 3 As you can see, two rows with value 18 in the 6 column of the 5 table were automatically updated to 14 because of the 21 action. 8) Delete 6 2 from the 4 table: 7 9) Verify the deletion:
10) Check the 5 table: 3 All products with 6 2 from the 5 table were automatically deleted because of the 26 action. SELECT * FROM categories;Code language: SQL (Structured Query Language) (sql)8 actionThese steps illustrate how the 44 and 45 actions work. 1) Drop both 4 and 5 tables: 0 2) Create the 4 and 5 tables: 1 The foreign key in the 5 table changed to 44 and 45 options. 3) Insert rows into the 4 table: 2 4) Insert rows into the 5 table: 2 5) Update 6 from 1 to 100 in the 4 table: 4 6) Verify the update:
7) Select data from the 5 table: The rows with the 6 1 in the 5 table were automatically set to 7 due to the 44 action. 8) Delete the 6 2 from the 4 table: 6 9) Check the 5 table: 3 The values in the 6 column of the rows with 6 2 in the 5 table were automatically set to 7 due to the 45 action. Drop MySQL foreign key constraintsTo drop a foreign key constraint, you use the 70 statement: 8 In this syntax:
Notice that 73 is the name of the foreign key constraint specified when you created or added the foreign key constraint to the table. To obtain the generated constraint name of a table, you use the 74 statement: 9 For example, to see the foreign keys of the 5 table, you use the following statement: 0 The following is the output of the statement: As you can see clearly from the output, the table 5 table has one foreign key constraint: 77 And this statement drops the foreign key constraint of the 5 table: 1 To ensure that the foreign key constraint has been dropped, you can view the structure of the products table: 0 Disabling foreign key checksSometimes, it is very useful to disable foreign key checks e.g., when you import data from a CSV file into a table. If you don’t disable foreign key checks, you have to load data into a proper order i.e., you have to load data into parent tables first and then child tables, which can be tedious. However, if you disable the foreign key checks, you can load data into tables in any order. To disable foreign key checks, you use the following statement: 3 And you can enable it by using the following statement: 4 In this tutorial, you have learned about the MySQL foreign key and how to create a foreign key constraint with various reference options. How to check if foreign key exists in table MySQL?To see foreign key relationships of a table: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA. KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db_name' AND REFERENCED_TABLE_NAME = 'table_name';
How do I know if a foreign key exists in a table?Using SQL Server Management Studio. Open the Table Designer for the table containing the foreign key you want to view, right-click in the Table Designer, and choose Relationships from the shortcut menu.. In the Foreign Key Relationships dialog box, select the relationship with properties you want to view.. How do I check if a foreign key exists in SQL?You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table has one or more foreign key constraints.
How do you check if a value exists in a table in MySQL?The syntax of the PostgreSQL EXISTS condition is as follows: WHERE EXISTS (subquery);. SELECT column1, column2… columnN.. FROM table1.. WHERE [NOT] EXISTS (. SELECT 1.. FROM table2.. WHERE column2= table1. column1.. |
How to check if foreign key exists in MySQL table?
Pos Terkait
Copyright © 2024 ketiadaan Inc.
