How to set MySQL foreign keys for beginners

The role of foreign keys

Maintaining data consistency, integrity, and the main purpose is to control the data stored in the foreign key table. To associate two tables, the foreign key can only reference the values ​​of the columns in the foreign table!

For example:

ab two tables

Table a contains customer number and customer name

Table b stores the orders of each customer.

With foreign keys

You can delete customer x from table a only after you are sure that there are no orders for customer x in table b.

Prerequisites for establishing a foreign key: The columns of this table must be of the same type as the foreign key (the foreign key must be the primary key of the foreign table).

Specify the primary key keyword: foreign key (column name)

Reference foreign key keywords: references <foreign key table name> (foreign key column name)

Event trigger restrictions: on delete and on update, can set parameters cascade (follow foreign key changes), restrict (restrict foreign key changes in the table), set Null (set null value), set Default (set default value), [default] no action

For example:

outTable table primary key id type int

Create a table with a foreign key:

  create table temp(
  id int,
  name char(20),
  foreign key(id) references outTable(id) on delete cascade on update cascade);

Note: Set the id column as a foreign key to refer to the id column of the foreign table outTable. When the value of the foreign key is deleted, the corresponding column in this table is filtered out. When the value of the foreign key is changed, the corresponding column value in this table is changed.

mysql foreign key setting method

MySQL foreign key setting method / when creating an index, you can specify the corresponding operation to be performed on the child table when deleting/updating the parent table.

Includes: restrict, cascade, set null and no action, set default.

• restrict,no action:
  Check foreign key constraints immediately. If there are matching records in the child table, the parent table related records cannot be deleted or updated.
• cascade:
  When the parent table is deleted/updated, the corresponding records in the child table are deleted/updated accordingly;
• set null:
  When the parent table is deleted/updated, the corresponding field of the child table is set to null. At this time, please note that the foreign key of the child table cannot be set to not null;
• set default:
  When the parent table has delete/update, the child table sets the foreign key to a default value, but InnoDB cannot recognize it. In fact, the default storage engine after MySQL 5.5 is InnoDB, so it is not recommended to set this foreign key method. If your environment MySQL is before 5.5 and the default storage engine is myisam, you can consider it.

Be careful when choosing set null, setdefault, and cascade, as incorrect operations may result in data loss.

If the above description is not clear enough, please refer to the following example.

The country table is the parent table, country_id is the primary key, city is the child table, and the foreign key is country_id, which corresponds to the primary key country_id of the country table.

create table country(
	country_id smallint unsigned not null auto_increment,
	country varchar(50) not null,
	last_update timestamp not null default current_timestamp on update current_timestamp,
	primary key(country_id)
)engine=INNODB default charset=utf8;

CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL auto_increment,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) on delete restrict ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

For example, for the two newly created tables above, the sub-table foreign key is specified as: on delete restrict ON UPDATE CASCADE mode. When the main table deletes records, if the sub-table has corresponding records, deletion is not allowed; when the main table updates records, if the sub-table has matching records, the corresponding records in the sub-table are updated accordingly.

eg:

insert into country values(1,'wq',now());
select * from country;
insert into city values(222,'tom',1,now());
select * from city;

delete from country where country_id=1;
update country set country_id=100 where country_id=1;
select * from country where country='wq';
select * from city where city='tom';

Summarize

This is the end of this article about how to set MySQL foreign keys. For more information about how to set MySQL foreign keys, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!