This is the seventh day of my participation in the August More text Challenge. For details, see: August More Text Challenge

A foreign key

  • Foreign key: Foreign key, an external key that is not in its own table
  • If a table has a field (other than a primary key) that points to the primary key of another table, that field is called a foreign key

Increase the foreign key

  • Foreign keys can be added at table creation time or after table creation, but the data is a concern

  • A table can have more than one foreign key

  • * Foreign key * foreign key * Foreign key * Foreign key * Foreign key * foreign key * foreign key *

create table my_foreign1(
	id int primary key auto_increment,
	name varchar(20) not null comment 'Student name',
	c_id int comment 'class id'.-- Normal field
	Add foreign key
	foreign key(c_id) references my_class(id)
)charset utf8;
Copy the code
  • Adding a foreign key after adding a new table: modifying the table structure
  • Alter table table name add [constraint foreign key name] REFERENCES parent table (primary key)
- create a table
create table my_foreign2(
id int primary key auto_increment,
name varchar(20) not null comment 'Student name',
c_id int comment 'class id'	-- Normal field
)charset utf8;

Add foreign key
alter table my_foreign2 add
-- Specify the foreign key name
constraint student_class_1
-- Specify the foreign key field
foreign key(c_id)
-- Reference the primary key of the parent table
references my_class(id);
Copy the code

Update and delete foreign keys

  • The foreign key cannot be modified. You can only delete the foreign key and then add it
  • Alter TABLE Table name drop Foreign key Foreign key name
  • A table can have multiple foreign keys, but their names cannot be the same. When deleting a table, you must specify the foreign key name
Delete the foreign key
alter table my_foreign1 drop  foreign key my_foreign1_ibfk_1;
Copy the code

Foreign key role

  • By default, foreign keys can be used in either the parent table or the word table (the table where the foreign key field is located).
  • When an operation is performed on a child table constraint, the operation will fail if the corresponding foreign key field does not match in the parent table.

Insert data: foreign key field does not exist in parent table
insert into my_foreign2 values(null.'Zhang Zizhong'.4); -- There are no 4 classes
insert into my_foreign2 values(null.'xiang yu'.1); 
insert into my_foreign2 values(null.'liu'.2); 
insert into my_foreign2 values(null.'han xin'.2); 
Copy the code
  • Parent table constraint: Operations on the parent table data (delete or change must involve the primary key itself) are not allowed if the corresponding primary key has been referenced in the child table data
Update the parent table record
update my_class set id = 4 where id = 1; -- Failed: id=1 record has been referenced by a student
update my_class set id = 4 where id = 3; -- Yes: no quotes
Copy the code

Foreign key conditions

  1. If innoDB is not the default storage engine, then the foreign key can be created successfully, but there is no constraint
  2. The field type (column type) of the foreign key field must be exactly the same as the primary key type of the parent table
  3. Foreign key names in a table must not be the same
  4. To add a foreign key field (data already exists), ensure that the data corresponds to the primary key requirement of the parent table

Foreign key constraints

  • Foreign key constraints are what foreign keys do
  • There are three constraint modes for foreign key constraints, all of which are for parent table constraints
    • District: In strict mode (default), the parent table cannot delete or update a record that has been referenced by the table data
    • Cascade: The operation on the parent table will delete the data associated with the child table
    • Set NULL mode: After an operation on the parent table, data (foreign key fields) corresponding to the child table is Set null
  • A common and reasonable approach (constraint schema) is to set the child table to empty when deleting, and the child table cascading operation when updating specifies the schema syntax
  • Foreign Key (Foreign key field) REFERENCES Parent table (primary key field) on delete set NULL on UPDATE CASCADE
  • Update operation: cascade update
  • Delete: Set this parameter to blank