mysql does not support foreign keys

Mysql supports foreign keys. In MySQL, foreign keys are mainly used to establish the relationship between the master table and the slave table. They can establish a connection for the data in the two tables and constrain the consistency and integrity of the data in the two tables; when the master table deletes a record, The corresponding record from the table must also be changed accordingly. A table can have one or more foreign keys, and the foreign key can be a null value. If it is not a null value, the value of each foreign key must be equal to a certain value of the primary key in the main table; and the number of columns in the foreign key and The corresponding data type must be the same as the primary key of the main table.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

mysql supports foreign keys.

MySQL foreign key (FOREIGN KEY)

A foreign key is a field in a specified table that matches another field in another table field. Foreign keys set constraints on data in related tables, which allows MySQL to maintain referential integrity.

Foreign keys are used to establish the association between the master table and the slave table, establish a connection for the data in the two tables, and constrain the consistency and integrity of the data in the two tables.

For two tables with an associated relationship, the table where the primary key in the associated field is located is the main table (parent table), and the table where the foreign key is located is the secondary table (child table).

When a record is deleted from the main table, the corresponding record from the table must also be changed accordingly. A table can have one or more foreign keys, and the foreign key can be null. If it is not null, the value of each foreign key must be equal to a certain value of the primary key in the main table.

Let’s take a look at the following database diagram from the sample database.

We have two tables: customers and orders. Each customer has zero or more orders, and each order can only belong to one customer. The relationship between the customers table and the orders table is one-to-many, with orders established by the foreign key in the table specified by the customerNumber field. The customerNumber field in the customers table is related to the customerNumber primary key field in the orders table.

The customers table is called the parent table or reference table, and the orders table is called the child table or reference table.

The foreign key can be a column or a group of columns. Columns in the child table usually reference primary key columns in the parent table.

A table can have multiple foreign keys, and each foreign key in a child table can reference a different parent table.

The rows in the child table must contain values ​​that exist in the parent table, for example, each order record in the orders table must have a value that exists in the customers table customerNumber. Therefore, multiple orders can reference the same customer, and this relationship is called one (customer) to many (orders) or one-to-many.

Sometimes, the child table and the parent table are the same. The foreign key refers to the primary key of the table, for example, the following employees table:

The reportTo column is a foreign key that references employeeNumber as the column of the primary key of the employees table to reflect the number of employees. A reporting structure between employees, that is, each employee reports to another employee, and employees can have zero or more direct reports. We have a tutorial on self-joins that can help you query data against such tables.

reportTo foreign key is also known as recursive or self-referential foreign key.

Foreign keys enforce referential integrity, helping you automatically maintain the consistency and integrity of your data. For example, you cannot create an order for a customer that does not exist.

Additionally, you can set up a cascade on the delete operation of the customerNumber foreign key so that when a customer is deleted in the customers table, all orders associated with the customer are also deleted. This saves time and effort using multiple DELETE statements or DELETE JOIN statements.

Same as delete, you can also define a cascade for the customerNumber foreign key on the update operation to perform cross-table updates without using multiple UPDATE statements or UPDATE JOIN statements.

Note: In MySQL, the InnoDB storage engine supports foreign keys, so you must create an InnoDB table to use foreign key constraints.

When mysql defines foreign keys, you need to comply with the following rules:

• The main table must already exist in the database, or it must be the current The table being created. If it is the latter case, the master table and the 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 main table.

• The primary key cannot contain null values, but null values ​​are allowed in foreign keys. That is, as long as every non-null value of the foreign key appears in the specified primary key, the contents of the foreign key are correct.

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

• The number of columns in the foreign key must be the same as the number of columns in the primary key of the main 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 main table.

Create a foreign key for the table

MySQL creates a foreign key syntax

The following syntax explains how to create a foreign key in CREATE TABLE Define foreign keys in the child table in the statement.

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

Let’s study the 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.

• The FOREIGN KEY clause specifies the columns in the child table that reference the primary key columns in the parent table. You can put a foreign key name after the FOREIGN KEY clause, or let MySQL create a name for you. Note that MySQL automatically creates the index using the foreign_key_name name.

• The REFERENCES clause specifies the parent table and columns referenced by the columns in the child table. The number of columns in the specified child table and parent table FOREIGN KEY and REFERENCES must be the same.

• The ON DELETE clause allows you to define the contents of records in the child table when records in the parent table are deleted. If you omit the ON DELETE clause and delete records in the parent table that contain records in the child table, MySQL will refuse to delete. In addition, MySQL also provides you with actions so that you can use additional options such as ON DELETE CASCADE , which asks MySQL to delete records in the child table. When a record in the parent table is deleted, the record will reference the record in the parent table. If you do not wish to delete related records in the child table, use the ON DELETE SET NULL operation instead. MySQL will set the foreign key column value in the child table to NULL when deleting records in the parent table, but the condition is that the foreign key column in the child table must accept NULL values. Note that MySQL will reject the delete if you use the ON DELETE NO ACTION or ON DELETE RESTRICT operation.

• The ON UPDATE clause allows you to specify what happens to rows in the child table when rows in the parent table are updated. You can omit the ON UPDATE clause to have MySQL reject any updates to rows in the child table when updating rows in the parent table. The ON UPDATE CASCADE operation allows you to perform cross-table updates, and when a row in the parent table is updated ON UPDATE SET NULL, the operation resets the value in the row in the child table to the value NULL. ON UPDATE NO ACTION or UPDATE RESTRICT action to reject any update.

MySQL Create 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 keys to the table

MySQL add foreign key syntax

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

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action;

MySQL Add Foreign Key Example

Now, let 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 the foreign key products to the table, use the following statement:

ALTER TABLE products
ADD FOREIGN KEY fk_vendor(vdr_id)
REFERENCES vendors(vdr_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

Now, the products table has two foreign keys, one refers to the categories table and the other refers to the vendors table.

[Related recommendations: mysql video tutorial]

The above is the detailed content of mysql does not support foreign keys. For more information, please follow other related articles on the PHP Chinese website!