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

This article mainly explains "what is the use of foreign keys in mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "what is the use of foreign keys in mysql"!

In mysql, a foreign key is one or more columns used to establish and enforce a link between data in two tables, indicating that a field in one table is referenced by a field in another table. Foreign keys impose restrictions on the data in related tables, enabling MySQL to maintain referential integrity.

Operating environment of this tutorial: Windows 7 system, MySQL8 version, Dell G3 computer.

Foreign keys are relative to primary keys.

A primary key uniquely identifies an attribute or attribute group of a row in a table. A table can have only one primary key, but it can have multiple candidate indexes. Primary keys often form referential integrity constraints with foreign keys to prevent data inconsistencies. The primary key can ensure that the record is unique and the primary key field is not empty. The database management system automatically generates a unique index for the primary key, so the primary key is also a special index.

A foreign key is one or more columns used to establish and enforce a link between data in two tables. A foreign key indicates that a field in one table is referenced by a field in another table. Foreign keys impose restrictions on the data in related tables, enabling MySQL to maintain referential integrity.

Foreign key constraints are primarily used to maintain data consistency between two tables. In short, a table's foreign key is the primary key of another table, and the foreign key links the two tables. In general, to delete a primary key in a table, you must first ensure that no other tables have the same foreign key (that is, no primary key in the table has a foreign key associated with it).

When defining foreign keys, the following rules apply:

The primary table must already exist in the database or be the table currently being created. If the latter is the case, the master table and slave table are the same table, such a table is called a self-referential table, and this structure is called self-referential integrity.

A primary key must be defined for the primary table.

Primary keys cannot contain nulls, but nulls are allowed in external keys. That is, the contents of a foreign key are correct as long as each non-null value of the foreign key appears in the specified primary key.

Specify a column name or combination of column names after the table name of the primary table. This column or combination of columns must be the primary key or candidate key of the primary table.

The number of columns in the foreign key must be the same as the number of columns in the primary key of the primary table.

The data type of the column in the foreign key must be the same as the data type of the corresponding column in the primary key of the primary table.

create a foreign key

MySQL Create Foreign Key Syntax

The following syntax explains how to define foreign keys in child tables in CREATE TABLE statements.

CONSTRAINT constraint_nameFOREIGN KEY foreign_key_name (columns)REFERENCES parent_table(columns)ON DELETE actionON UPDATE action

Let's look at the above syntax in more detail:

The CONSTRAINT clause allows you to define constraint names for foreign key constraints. If you omit it, MySQL will automatically generate a name.

FOREIGN KEY clause specifies columns in child tables that reference primary key columns in parent tables. You can place a foreign key name after the FOREIGN KEY clause, or have MySQL create a name for you. Note that MySQL automatically creates an index with the name foreign_key_name.

The REFERENCES clause specifies references to columns in the parent table and its child tables. The child table and parent table specified in FOREIGN KEY and REFERENCES must have the same number of columns.

The ON Delete clause allows you to define how records in child tables perform actions when records in parent tables are deleted. If you omit the ON Delete clause and delete records in the parent table, MySQL refuses to delete the associated data in the child table. In addition, MySQL also provides operations so that you can use other options, such as ON Delete CASCADE, when deleting records in the parent table, MySQL can delete records in child tables that reference records in the parent table. If you do not want to delete related records in the child table, use the ON Delete SET NULL operation instead. When a record in the parent table is deleted, MySQL sets the foreign key column value in the child table to NULL, provided that the foreign key column in the child table must accept NULL values. Note that MySQL rejects deletion if the ON Delete NO ACTION or ON Delete RESTRICT action is used.

The ON UPDATE clause allows you to specify how rows in child tables perform actions when rows in the parent table are updated. When a row in the parent table is updated, you can omit the ON UPDATE clause to have MySQL reject any updates to the row in the child table. The ON UPDATE CASCADE operation allows you to perform crosstab updates, and when updating rows in a parent table, the ON UPDATE SET NULL operation resets the values in rows in child tables to NULL values. The ON UPDATE NO ACTION or UPDATE RESTRICT action rejects any updates.

MySQL Creating a Table Foreign Key Example

The following example creates a dbdemo database and two tables: categories and products. Each category has one or more products, and each product belongs to only one category. The cat_id field in the products table is defined as a foreign key with UPDATE ON CASCADE and Delete ON RESTRICT operations.

CREATE DATABASE IF NOT EXISTS dbdemo;USE dbdemo;CREATE TABLE categories( cat_id int not null auto_increment primary key, cat_name varchar(255) not null, cat_description text) ENGINE=InnoDB;CREATE TABLE products( prd_id int not null auto_increment primary key, prd_name varchar(355) not null, prd_price decimal, cat_id int not null, FOREIGN KEY fk_cat(cat_id) REFERENCES categories(cat_id) ON UPDATE CASCADE ON DELETE RESTRICT)ENGINE=InnoDB; Add foreign key

MySQL Add Foreign Key Syntax

To add a foreign key to an existing table, use the ALTER TABLE statement with the foreign key definition syntax described above:

ALTER table_nameADD CONSTRAINT constraint_nameFOREIGN KEY foreign_key_name(columns)REFERENCES parent_table(columns)ON DELETE actionON UPDATE action;

MySQL Add Foreign Key Example

Now we add a new table called vendors and change the products table to include the vendor ID field:

USE dbdemo;CREATE TABLE vendors( vdr_id int not null auto_increment primary key, vdr_name varchar(255))ENGINE=InnoDB;ALTER TABLE products ADD COLUMN vdr_id int not null AFTER cat_id;

To add a foreign key to the products table, use the following statement:

ALTER TABLE productsADD FOREIGN KEY fk_vendor(vdr_id)REFERENCES vendors(vdr_id)ON DELETE NO ACTIONON UPDATE CASCADE;

The products table now has two foreign keys, one that references the categories table and one that references the vendors table.

Delete MySQL foreign keys

You can also delete foreign keys using the ALTER TABLE statement, as follows:

ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

In the above statement:

First, specify the name of the table from which you want to delete the foreign key.

Second, place the constraint name after the DROP FOREIGN KEY clause.

Note that constraint_name is the name of the constraint specified when creating or adding a foreign key to the table. If you omit it, MySQL generates the constraint name for you.

To get the constraint name of the generated table, use the SHOW CREATE TABLE statement, as follows:

SHOW CREATE TABLE table_name;

For example, to view foreign keys for the products table, use the following statement:

SHOW CREATE TABLE products;

The following is the output of the statement:

CREATE TABLE products ( prd_id int(11) NOT NULL AUTO_INCREMENT, prd_name varchar(355) NOT NULL, prd_price decimal(10,0) DEFAULT NULL, cat_id int(11) NOT NULL, vdr_id int(11) NOT NULL, PRIMARY KEY (prd_id), KEY fk_cat (cat_id), KEY fk_vendor(vdr_id), CONSTRAINT products_ibfk_2 FOREIGN KEY (vdr_id) REFERENCES vendors (vdr_id) ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT products_ibfk_1 FOREIGN KEY (cat_id) REFERENCES categories (cat_id) ON UPDATE CASCADE) ENGINE=InnoDB;

The products table has two foreign key constraints: products_ibfk_1 and products_ibfk_2.

You can delete a foreign key from the products table using the following statement:

ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;ALTER TABLE products DROP FOREIGN KEY products_ibfk_2;MySQL disable foreign key checking

Sometimes it is useful to disable foreign key checking for some reason (for example, importing data from a CSV file into a table). If foreign key checking is not disabled, the data must be loaded in the correct order, that is, the data must first be loaded into the parent table and then imported into the child table, which can be tedious. However, if foreign key checking is disabled, imported data can be loaded in any order.

You cannot delete tables referenced by foreign key constraints unless foreign key checking is disabled. When you delete a table, you also delete any constraints defined for the table.

To disable foreign key checking, use the following statement:

SET foreign_key_checks = 0;

Of course, you can enable it with the following statement:

SET foreign_key_checks = 1; At this point, I believe that everyone has a deeper understanding of "what is the use of foreign keys in mysql". Let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

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.