MySQL foreign key constraint

First, let's build two test tables

First create the main table

CREATE  TABLE   my_class(
     id  INT  PRIMARY KEY  ,  --  Primary key 
     `name` VARCHAR(32) NOT NULL DEFAULT ''
     );
  

  Creating slave tables

CREATE TABLE my_stu (
     id  INT PRIMARY KEY ,  --   Student number 
     `name` VARCHAR(32) NOT NULL DEFAULT  '',
      class_id INT,    --   If ( Foreign keys ) If it's not empty, you can't 
     --   Next, specify the foreign key relationship 
     
    FOREIGN KEY (class_id)  REFERENCES my_class(id)
)  

First record the definition of foreign keys :

1. Foreign keys are used for Define the relationship between master table and slave table : Foreign key constraints should be defined on the slave table ,

  From the table

  Found a field from the table (class_id), This field is used to establish the foreign key relationship ‘ The key ’,

Actually, there's a... On the table class_id This field The value of the inside Point to a field in the main table (id Field ) Value ,

That is, in the foreign key table   The class number of each student class_id（ The value ） Namely ( Main table ) The class code in the class table id Value  

First record the necessary conditions for establishing a foreign key relationship :

  The value of the foreign key field , Must have appeared in the primary key field ,( Like in the picture class_id The field value of is 300 In the main table id There has been... In this field )

Or for null    [ If the foreign key field is allowed to be null]

 

 

 

Once the foreign key relationship is established between the slave table and the master table , Want to delete a piece of information in the main table , First delete the record of the table where the foreign key is located ( From the table ),

Then delete the record in the main table

ps： Here, a record in the main table is deleted directly , Because of... In the main table id This field is the same as that from the table class_id The foreign key relationship established by this field cannot be deleted

The correct approach :

First delete the record in the table where the foreign key is located ( From the table id) Record ,

 

class_id=300 The record of is gone

Then delete the main table （ Records in the class table ） Find out       

 

reason : From table The foreign key Record of the value of the field     There is no association in the main table

That is to say , From the values of foreign keys class_id Field =？？？    And   In the main table id=？？？ This field value has no relationship