Mysql ForeignKey Related

A foreignkey is a relationship that is established when a one-to-many relationship:

a foreign key is a field in a table that matches another field in another table. foreign keys impose restrictions on data in related tables, which allows MySQL to maintain referential integrity. 　　

Operation:

　　

Can you read the picture?

See the code directly below:

　　

We have two tables: customers and the orders.  each customer has 0 or more orders, and each order can belong to only one customer. customers The relationship between a table and a orders table is a one-to-many relationship and is orders determined by the customerNumber field specifies that the foreign key in the table is established . The fields in the table are customerNumber orders related customerNumber to the primary key fields in the table customers .

the customers table is called Parent Table or be Referencing Tables , the orders table is called child table or Referencing Tables .

A foreign key can be a column or a set of columns. The columns in the child table typically refer to the primary key columns in the parent table .

A table may have more than one foreign key, and each foreign key in the child table may 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 orders in the table must have a table customerNumber The values customers that exist in . Therefore, multiple orders can refer to the same customer, a relationship known as a (customer) multiple (order) or one-to-many.

Sometimes, the child table and the parent table are the same. The foreign key returns to the table's primary key, such as the following employees table:

the reportTo column is a foreign key, it refers to the employeeNumber is a employees The primary key of the table to reflect the reporting structure between employees, that is, each employee reports to another employee, and the employee can have 0 or more direct reports. We have a specific tutorial on self-joins that can help you query data based on this form.

the reportTo foreign keys are also known as Recursive or self-referencing foreign key.

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

Alternatively, you can provide customerNumber the foreign key sets the cascade delete operation so that when you delete customers table , all orders associated with the customer are also deleted. This saves you the time and effort to use multiple DELETE statements or delete join statements .

As with Delete, you can also define a cascading update operation customerNumber to foreign keys perform cross-table updates without using multiple Update statement or update join statement .

in MySQL, the InnoDB storage engine supports foreign keys, so you must create a InnoDB table to use FOREIGN key constraints.

To add a foreign key code:

　　

CONSTRAINT constraint_name FOREIGN KEY foreign_key_name (columns) REFERENCES parent_table (columns)  on DELETE Action  on UPDATE Action

View Code

• the CONSTRAINT clause allows you to define a constraint name for a foreign key constraint. If you ignore it, MySQL will automatically generate a name.
• the FOREIGN KEY clause Specifies the column in the child table that references the primary key column in the parent table. You can FOREIGN KEY Place a foreign key name after the clause, or let it create a name for MySQL. Note that MySQL automatically creates an index with a foreign_key_name name .
• the REFERENCES clause to specify the column that the parent table and its child tables refer to. the number of columns in the specified child table and parent table FOREIGN KEY REFERENCES must be the same.
• the on delete clause allows you to define what happens in a child table when a record in the parent table is deleted. If you omit the on delete clause and delete the records in the parent table that have records in the child table, MySQL rejects the deletion. In addition, MySQL provides you with some actions so that you can use other options, such as on DELETE CASCADE    Requires MySQL to delete records in the parent table when a record in the parent table is deleted from the child table. If you do not want to delete related records in the child table, you can use the on DELETE SET NULL operation. MySQL Sets the foreign key column value in the child table to null when a record in the parent table is deleted, the foreign key column in the child table must accept the null The condition of the value. Notice that if you use on DELETE NO ACTION or on DELETE RESTRICT operation, MySQL will reject the deletion.
• the ON UPDATE clause allows you to specify rows in a child table that occur when a row in the parent table is updated. when ON UPDATE a row in the parent table is updated, You can omit the clause so that MySQL denies any updates to the rows in the child table. This ON UPDATE CASCADE Action allows you to perform a cross-table update when the row in the parent table is ON UPDATE SET NULL updated, the The action resets the values in the rows in the child table to values NULL . This ON UPDATE NO ACTION or UPDATE RESTRICT action rejects any updates.

MySQL CREATE TABLE foreign key example

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

CREATE DATABASE IF  not EXISTSDbdemo; UseDbdemo;CREATE TABLECategories (cat_idint  not NULLAuto_incrementPrimary Key, Cat_namevarchar(255) not NULL, Cat_descriptiontext) ENGINE=InnoDB;CREATE TABLEProducts (prd_idint  not NULLAuto_incrementPrimary Key, Prd_namevarchar(355) not NULL, Prd_pricedecimal, cat_idint  not NULL,   FOREIGN KEYFk_cat (cat_id)REFERENCEScategories (cat_id) on UPDATE CASCADE    on DELETE RESTRICT) ENGINE=InnoDB;

View Codeto add a foreign key to a tablemysql Add foreign key syntax

to add a foreign key to an existing table, you can use the The ALTER TABLE statement and the above foreign key definition syntax:

ALTER table_name ADD CONSTRAINT constraint_name FOREIGN KEY foreign_key_name (columns) REFERENCES parent_table (columns)  on DELETE Action  on UPDATE Action;

View Codemysql Add foreign key example

now, let's add a new table named vendors and Change products table to include the Vendor ID field:

 UseDbdemo;CREATE TABLEVendors (vdr_idint  not NULLAuto_incrementPrimary Key, Vdr_namevarchar(255) ) ENGINE=InnoDB;ALTER TABLE ProductsADD COLUMNvdr_idint  not NULLAfter cat_id;

View Code

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

2 3 4 5 ALTER TABLE  Products ADD FOREIGN KEY Fk_vendor (vdr_id) REFERENCES Vendors (vdr_id)  on DELETE NO ACTION  on UPDATE CASCADE;

View Code

now, the products table has two foreign keys, one categories table points to the table, and the other points to vendors table.

remove MySQL foreign key

You can also use the ALTER TABLE statement to delete the foreign key as follows:

ALTER TABLE  DROPFOREIGNKEY constraint_name;

View Code

In the above statement:

• First, specify the name of the table from which you want to remove the foreign key.
• Second, you put the constraint name in the DROP FOREIGN KEY after the clause.

Note that this constraint_name is the name of the constraint that you specify when you create or add a foreign key to a table. If you ignore it, MySQL will generate a constraint name for you.

to get the build constraint name for a table, SHOW CREATE TABLE This statement is used as follows :

CREATE TABLE table_name;

For example, to view products Table 's foreign key , you can use the following statement:

    shows  CREATETABLE products;

Both on delete and on update have restrict,no Action, cascade,set null property. Now explain the meaning of their attributes separately.

• On DELETE

Restrict (constraint): When the corresponding record is deleted in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, and if there is one, it is not allowed to delete.

No action: The meaning is the same as restrict. That is, if there is data from, the deletion of master data is not allowed.

Cascade (cascading): When the corresponding record is deleted in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, and if there is one, delete the record in the child table (that is, the table that contains the foreign key).

Set NULL: When the corresponding record is deleted in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, and if so, set the foreign key value in the child table to null (but this requires that the foreign key is allowed to take null)

• On UPDATE

Restrict (constraint): When the corresponding record is updated in the parent table (that is, the source table of the foreign key), first checks whether the record has a corresponding foreign key and, if there is one, does not allow the update.

No action: mean with restrict.

Cascade (cascading): When the corresponding record is updated in the parent table (that is, the source table of the foreign key), first checks whether the record has a corresponding foreign key, and if so, updates the record in the child table (that is, the table that contains the foreign key).

Set NULL: When the corresponding record is updated in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key and, if there is one, set the foreign key value in the child table to null (this requires that the foreign key is allowed to take null).

Note: The difference between NO action and restrict: Only in the case of individual cases, the former is executed after the action of the other constraint, which has the highest priority execution.

Mysql ForeignKey Related