MySQL creates a data table and establishes a primary foreign key relationship

There are a few things to keep in mind when establishing a primary foreign key for MySQL data tables:

The storage engine for the two tables that need to establish a primary foreign key relationship must be innodb.

Foreign key columns and reference columns must have similar data types, that is, data types that can be implicitly converted.

Foreign key columns and reference columns must be indexed and MySQL will automatically create indexes if the foreign key columns do not exist.

The SQL statement creates a data table and sets the primary foreign key relationship

Create TableDemo. Chinesecharinfo (IDint      not NULLauto_increment, Hanzivarchar(Ten) not NULL,    Primary Key(ID)) engine=InnoDB auto_increment=1 defaultCharSet=UTF8 Collate=utf8_general_ci;Create TableDemo. Chinesepinyininfo (IDint      not NULLauto_increment, Charidint     NULL, Pinyinvarchar(Ten)NULL, TonetinyintUnsignedNULL,    Primary Key(ID),--mode One: Do not specify a foreign key name, the database is automatically generated    Foreign Key(Charid)ReferencesChinesecharinfo (ID) on Delete Cascade  on Update Cascade         --Mode Two: Specify the foreign key name as (Fk_name)    --constraint fk_name foreign key (Charid) references Chinesecharinfo (ID) on DELETE cascade on UPDATE Cascade) Engine=InnoDB auto_increment=1 defaultCharSet=UTF8 Collate=Utf8_general_ci;

Second, when the data table already exists, it is necessary to use the following method to establish the primary foreign key relationship

 --  alter  table  demo. Chinesepinyininfo add  constraint  fk_name Span style= "COLOR: #0000ff" >foreign  key  (Charid) references   Chinesecharinfo (ID);  --  alter  table  demo. Chinesepinyininfo add  foreign  key  (Charid) references  chinesecharinfo (ID); 

Third, remove the primary foreign KEY constraint

--remove self-growth by modifying the properties of a column, the first (ID) is the original column name, the second (ID) is the new column nameAlter TableDemo. Chinesepinyininfo Change ID IDint  not NULL;--Delete Table (demo. Chinesepinyininfo), if the primary key column is self-increment, you need to first delete the column's self-growthAlter TableDemo. ChinesepinyininfoDrop Primary Key; --Delete Table (demo. Chinesepinyininfo) with a foreign key named (Fk_name)Alter TableDemo. ChinesepinyininfoDrop Foreign KeyFk_name;

Iv. constraints of the primary foreign key relationship

If the child table attempts to create a foreign key value that does not exist in the primary table, the database rejects any insert or update operations.

If the primary table attempts to update or delete any foreign key values that exist or match in any of the child tables, the final action depends on the on delete and on update options in the FOREIGN key constraint definition.

Both on delete and on update have the following four kinds of actions.

Cascade : The primary table deletes or updates the corresponding data rows, and the child table deletes or updates the rows that match the primary table, that is, cascading deletes, updates.  setnull: The primary table deletes or updates the corresponding data, and the child table also sets the foreign key column of the row that matches the primary table to null. Invalid when the foreign key column is set to NOT NULL. No action: the database refuses to delete or update the main table.  Restrict: The database refuses to delete or update the main table. If an on delete or on update action is not specified, the default action on delete or on update is restrict.

MySQL creates a data table and establishes a primary foreign key relationship