Shulou(Shulou.com)06/02 Report--

This article introduces you to mysql foreign key setting method is what, the content is very detailed, interested friends can refer to reference, hope to be helpful to everyone.

Role of foreign key

Maintaining data consistency, integrity, and primary purpose is to control the data stored in foreign key tables. Make two tables form an association, foreign key can only reference the column value in the table!

For example:

a b Two tables

a table contains customer number, customer name

b table contains orders for 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 foreign key: The columns of this table must be of the same type as the foreign key (the foreign key must be a foreign key).

Specify primary key keyword: foreign key(column name)

Foreign key references: references (foreign key column names)

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

For example:

outTable Table primary key id type int

Create a table with foreign keys:

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

Description: Set the id column as a foreign key to refer to the id column of the external table. When the value of the foreign key is deleted, the corresponding column in this table is screened out. When the value of the foreign key changes, the corresponding column value in this table changes.

mysql foreign key setting mode

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.

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, delete/update operations cannot be performed on the associated records in the parent table.

cascade：

When parent table delete /update, corresponding records of child table delete/update accordingly;

set null:

When the parent table is deleted/updated, the corresponding field of the child table is set null. At this time, 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 identify 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 is before mysql 5.5 and the default storage engine is myisam, consider it.

Be careful when selecting set null ,setdefault,cascade, as data loss may occur due to incorrect operation.

If the above description is not clear, see the following example.

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

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 above two newly created tables, the foreign key of the child table is specified as: on delete restrict ON UPDATE CASCADE mode. When deleting records in the main table, if there are corresponding records in the child table, the deletion is not allowed; when updating records in the main table, if there are matching records in the child table, the corresponding records in the child 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';

About mysql foreign key setting is what to share here, I hope the above content can have some help for everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.