MySQL (6), mysql

MySQL (6), mysql
1 foreign key

• Foreign key: foreign key, the external key (the key is not in the table itself). If a field (non-primary key) in a table points to the primary key of another table, this field is called a foreign key.

 

1.1 add Foreign keys

• The foreign key can be added when the table is created or after the table is created (but the data issue should be considered ).

 

• Solution 1: Add a foreign key when creating a table. After all the table fields, use the foreign key (foreign key field) references External table (primary key field );

-- Create table my_class (id int primary key AUTO_INCREMENT, c_name VARCHAR (20) not null, room VARCHAR (20 )); -- create table my_student1 (id int primary key AUTO_INCREMENT, name varchar (20) not null, c_id INT, CONSTRAINT fk_c_id foreign key (c_id) REFERENCES my_class (id ));

 

• Solution 2: Add a foreign key after adding a table, so you need to modify the table structure.

Alter table name add [constraint foreign key name] foreign key (foreign key field) references parent table (primary key field );

-- Create table my_class (id int primary key AUTO_INCREMENT, c_name VARCHAR (20) not null, room VARCHAR (20 )); -- create table my_student2 (id int primary key AUTO_INCREMENT, name varchar (20) not null, c_id INT); alter table my_student2 add constraint fk_c_id foreign key (c_id) REFERENCES my_class (id );

 

1.2 modify and delete Foreign keys

• Foreign keys cannot be modified: they can only be deleted before being added.

Alter table Name drop foreign key name; -- a table can have multiple foreign keys, but the names cannot be the same

ALTER TABLE my_student2 DROP FOREIGN KEY fk_c_id;

 

1.3 foreign key

• The foreign key has two functions by default:
  • A write operation on the data in the parent table (both deletion and modification must be performed on the primary key itself). If the corresponding primary key has been referenced by the data in the word table, therefore, the operation is not allowed.
  • A Word Table (the table where the foreign key field is located): When writing (adding and modifying) the word table data, if the corresponding foreign key field cannot find the corresponding match in the parent table, the Operation will fail.

 

1.4 foreign key conditions

• Foreign keys must exist: the storage engine of the table must be innodb. If it is not the innodb Storage engine, the foreign key can be created successfully without any constraints.
• The field type (column type) of the foreign key field must be exactly the same as the primary key type of the parent table.
• The foreign key names in a table cannot be repeated.
• To add a foreign key field (data already exists), ensure that the data matches the primary key requirements of the parent table.

 

1.5 foreign key constraints

• Foreign key constraint: it is the role of a foreign key.

 

• The foreign key constraint has three constraints: both are for the parent table.
  • District: the strict mode (default). The parent table cannot delete or update a record (primary key) that has been referenced by the quilt table ).
  • Cascade: cascade mode. The operations on the parent table and the associated data of the corresponding sub-table also change.
  • Set null: In the null mode, the data (foreign key) of the child table is null after the operation of the parent table.
• Generally, when the parent table is deleted, the child table is left empty. When the update is performed, the child table is cascade.

Constraint foreign key name foreign key (foreign key field) references primary table (primary key) on delete set null;

Constraint foreign key name foreign key (foreign key field) references primary table (primary key) on update cascade;

Constraint foreign key name foreign key (foreign key field) references primary table (primary key) on delete set null on update cascade;

 

• The premise for deleting NULL: The foreign key field is null (if the condition is not met, the foreign key cannot be created ).

 

• Although foreign keys are powerful and can carry out various constraints, for java, foreign key constraints reduce java's controllability of data. Therefore, in actual development, the cascade mode and null mode of foreign keys are rarely used.

 

2. Joint Query