Mysql foreign key constraints

MySQL has two common engine types: MyISAM and InnoDB. Currently, only the InnoDB engine type supports foreign key constraints. The syntax for defining foreign key constraints in InnoDB is as follows:

[CONSTRAINT [symbol] FOREIGN KEY
[Index_name] (index_col_name ,...)
REFERENCES tbl_name (index_col_name ,...)
[On delete reference_option]
[On update reference_option]

Reference_option:
RESTRICT | CASCADE | set null | the usage of the no action foreign key must meet the following conditions:

1. Both tables must be InnoDB tables without temporary tables.

2. The corresponding columns for establishing foreign key relationships must have similar InnoDB internal data types.

3. Indexes must be created for the columns corresponding to the foreign key relationship.

4. If the CONSTRAINT symbol is explicitly given, it must be unique in the database. If no explicit statement is provided, InnoDB is automatically created.

If a child table tries to create a foreign key value that does not exist in the parent table, InnoDB rejects any INSERT or UPDATE operations. If the parent table tries to UPDATE or DELETE any foreign key values that exist or match in any child table, the final action depends ON the on update and on delete options in the foreign key constraint definition. InnoDB supports five different actions. If on delete or on update is not specified, the default action is RESTRICT:

1. CASCADE: deletes or updates corresponding rows from the parent table, and automatically deletes or updates matched rows from the table. Both on delete canscade and on update canscade are supported by InnoDB.

2. set null: delete or update the corresponding row from the parent table, and SET the foreign key column in the child table to NULL. Note that these foreign key columns are valid only when they are NOT set to not null. Both on delete set null and on update set null are supported by InnoDB.

3. no action: InnoDB refuses to delete or update the parent table.

4. RESTRICT: refuse to delete or update the parent table. The effect of specifying RESTRICT (or no action) is the same as that of ignoring the on delete or on update options.

5. set default: InnoDB currently does not support this function.

The maximum number of foreign key constraints is exceeded in the following two cases:

1) when the parent table is updated, the child table is also updated. When the parent table is deleted, if the child table has matched items, the deletion fails;

2) when the parent table is updated, the child table is also updated. When the parent table is deleted, the matching items of the child table are also deleted.

In the previous case, in the foreign key definition, we use on update cascade on delete restrict; in the latter case, we can use on update cascade on delete cascade.

InnoDB allows you to use alter table to add a new foreign key to an existing TABLE:

Alter table tbl_name
ADD [CONSTRAINT [symbol] FOREIGN KEY
[Index_name] (index_col_name ,...)
REFERENCES tbl_name (index_col_name ,...)
[On delete reference_option]
[On update reference_option]

InnoDB also supports the use of alter table to delete foreign keys:
Alter table tbl_name drop foreign key fk_symbol;

Constraints

Constraints ensure data integrity and consistency
Constraints are divided into table-level constraints and column-level constraints.
Constraint types include -- not null (non-empty constraint) -- primary key (primary key constraint) -- unique key (UNIQUE constraint) -- DEFAULT (DEFAULT constraint) -- foreign key (foreign key constraint)

Analysis of foreign key constraints

Foreign key ensures data consistency and integrity. One-to-one or one-to-many relationships are implemented.

The parent and child tables must use the same storage engine and temporary tables are not allowed.
The data table storage engine can only be InnoDB
The foreign key column and the reference column must have similar data types. The length of the number or whether there are symbols must be the same, and the length of the characters can be different.
Indexes must be created for the foreign key column and reference column. If the foreign key column does not have an index, MySQL automatically creates an index.

Displays the created data of a data table.

Show create table tbl_name;

Show data table indexes

Show indexes from tbl_name;

Create two tables with foreign key constraints

'''Create TABLE provinces (id smallint unsigned primary key AUTO_INCREMENT, pname VARCHAR (20) not null );

Create table users (id smallint unsigned primary key AUTO_INCREMENT, username VARCHAR (10) not null, pid smallint unsigned, foreign key (pid) REFERENCES provinces (id ));

'''The foreign key and primary key data types must always be like the pid SMALLINT corresponding to pname SMALLINT, and the symbol bit must also be the same as the table with foreign keys. the column automatically creates an index.

Foreign key constraints

CASCADE: deletes or updates matched rows from the parent table and automatically deletes or updates the matched rows in the child table.
Set null: delete or update rows from the parent table, and SET the foreign key column NULL in the child table. If this option is used, make sure that the child table does NOT specify not null.
RESTRICT: the operation to delete or update the parent table is denied.
No action: Standard SQL keyword, which is the same as RESTRICT in MYSQL.
Foreign key constraints only support the innodb engine

Create table user1 (id smallint unsigned primary key AUTO_INCREMENT, username VARCHAR (10) not null, pid smallint unsigned, foreign key (pid) REFERENCES provinces (id) on delete cascade ); you need to enable the on delete operation with parameters

Add data

''' INSERT provinces (pname) VALUES ("A"); INSERT provinces (pname) VALUES ("B"); INSERT provinces (pname) VALUES ("C ");

// Insert user data

INSERT user1 (username, pid) VALUES ("Tom", 3); ''' to INSERT user data, you only need to set the id of the foreign key.

If the record is not successfully written, but the number increases automatically

Delete record

Delete from provinces WHERE id = 3 if the record with the provinces id is 3; then the corresponding sub-table with the foreign key id 3 will also be deleted.

Table-level constraints and column-level constraints

A column-level constraint is used to create a data column.
A table-level constraint is a constraint created on multiple data columns.
Column-level constraints can be declared either when a column is defined or after a column is defined.
Table-level constraints can only be declared after Column definition.
Modify a data table

Add a single column

Alter table tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name] FIRST indicates that the inserted COLUMN will be at the beginning. AFTER col_name is AFTER a COLUMN

Add single column data

Show columns from user1; alter table user1 ADD age int unsigned not null default 10; alter table user1 ADD password VARCHAR (32) not null after username; // Insert alter table user1 ADD truename VARCHAR (20) not null first after a column; DEFAULT: when the data is NOT explicitly indicated. default data UNSIGNED: indicates whether the numeric type is signed.

Add multiple columns

The location cannot be specified when multiple columns are added.

Alter table tbl_name ADD [COLUMN] (col_name column_definition );

Add multiple columns of data to user1

Alter table user1 ADD (num int unsigned not null default 1, sex ENUM ("male", "female") not null default "male ");

Delete column

Alter table user1 DROP truename;

Delete multiple columns

Alter table user1 DROP truename, DROP password;

When you modify a column, you can delete it and add it again. The column is separated by commas.

Add columns after deletion

Alter table user1 DROP num, ADD pm float unsigned default 15;

Add primary key constraints

Alter table tbl_name ADD [CONSTRAINT [symbol] primary key [index_type] (index_col_name ,...)

Example 1

Create table user2 (username VARCHAR (20) not null, pid smallint unsigned); // ADD the primary key alter table user2 ADD id smallint unsigned; // ADD the primary key constraint alter table user2 add constraint PK_user2_id primary key (id); CONSTRAINT: used to start an alias

Add unique constraint

Alter table tbl_name ADD [CONSTRAINT [symbol] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name ,...)

Example 1

Alter table user2 add unique (username );

Add foreign key constraints

Example 1

For pid in user2, see provinces; alter table user2 add foreign key (pid) REFERENCES provinces (id );

Add/delete default constraints

Alter table tbl_name ALTER [COLUMN] col_name {set default literal | drop default}

Example 1

Alter table user2 ADD age tinyint unsigned not null; alter table user2 ALTER age set default 15; add default constraints to age

Delete primary key constraints

Any TABLE has only one primary key alter table user2 drop primary key;

Delete primary key index

Alter table user2 drop index username; only indexes are deleted.

Show Index list name

SHOW INDEXes FROM user2;

Delete foreign key constraints

The delete constraint is the name of the delete constraint alter table tbl_name drop foreign key fk_symbok.

Example

''' // Query the foreign key name

Show create table user2; // find the pid's foreign key name user2_ibfk_1; then delete it.

Alter table user2 drop foreign key user2_ibfk_1;

Although the foreign key is absent, the INDEX still exists. If you do not want to delete the INDEX, you can alter table user2 drop index pid ;'''

Modify column definitions and rename data tables

Modify column definitions

Alter table tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

Modify column order

Alter table user2 MODIFY id smallint unsigned not null first; column_definition indicates that the column definition remains unchanged.

Modify column definition-example

Alter table user2 MODIFY id tinyint unsigned not null; modifying column definitions may cause data loss.

Modify column name

Alter table tbl_name CHANGE [COLUMN] old_col_name new col_name column_definition [FIRST | AFTER col_name]

Alter table user2 CHANGE pid p_id tinyint unsigned not null;

Rename a data table

1. alter table tbl_name RENAME [TO | AS] new_tbl_name;

2. rename table tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2];

Example ''' alter table user2 RENAME users2;

Rename table users2 TO user2 ;'''

Multiple foreign keys exist:
The product_order table has foreign keys for the other two tables.
A foreign key references the double row index in a product table. Another single row index referenced in the customer table:
Create table product (category int not null, id int not null,
Price DECIMAL,
Primary key (category, id) TYPE = INNODB;
Create table customer (id int not null,
Primary key (id) TYPE = INNODB;
Create table product_order (no int not null AUTO_INCREMENT,
Product_category int not null,
Product_id int not null,
Customer_id int not null,
Primary key (no ),
-- Double Foreign key
INDEX (product_category, product_id ),
Foreign key (product_category, product_id)
REFERENCES product (category, id)
On update cascade on delete restrict,
-- Single foreign key
INDEX (customer_id ),
Foreign key (customer_id)
REFERENCES customer (id) TYPE = INNODB;
(6) description:
1. If on update/delete is not declared, the restrict method is used by default.
2. For foreign key constraints, it is best to use: on update cascade on delete restrict.

Summary

Constraint-converted to not null, primary key, unique key, DEFAULT, and foreign key by function-converted to table-level constraint and column-level constraint by number of data columns

Modify data tables-operation on fields: add/delete fields, modify column definitions, modify column names, and so on-operation on constraints: add/delete constraints. -operations on data tables: renaming a data table (two methods ).

Column-level constraint: it can only be applied to one column. Table-level constraints: can be applied to one or more columns in a table.

The DEFAULT constraint (DEFAULT) and non-NULL constraint (not null) do NOT have table-level constraints.