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

This article mainly shows you how to use mysql foreign keys, the content is easy to understand, I hope you can learn, after learning there will be a harvest, the following let the editor take you to take a look at it.

What is a foreign key:

A foreign key is a primary key in which a field in a table points to another table, so that field can be called a foreign key. A table can have multiple foreign keys. Foreign keys are used to constrain the relationship between tables, it can be said that foreign keys are mapping relations between tables, which can help us to deal with the tightness and existence of the relationship between tables (for example, the cid class number of the student table and the id of the class table to establish an association, cid should not exist, if we do not increase the foreign key cid, do not establish a relationship, we do not know that the class number does not exist. ). In other words, foreign keys tell the database system, we think that the relationship, simple data, the system does not know the actual meaning, foreign keys is to tell the system how to deal with their relationship. Therefore, the core of foreign keys is constraints. The addition of foreign keys: the premise of creating a foreign key is that the field is first an index, and if not, creating a foreign key will create a normal index [so you can ignore it]. Another prerequisite for creating a foreign key is that the "pointing table" has been created, and for a table that does not exist, you will not be able to use the foreign key. The way to increase: 1. Define when you create the table, using the foreign key (foreign key field) references to point to the table (primary key) after all field definitions, such as 2. 5. You can also modify the field to add: alter table table name add [constraint foreign key name] foreign key (foreign key field) references parent table (primary key field) Constraint foreign key name: can help define the foreign key name, but it is not recommended, because the foreign key name is required to be unique, and the use of system customization will never be repeated, create table student (id int primary key auto_increment,name varchar (15) not null,gender varchar (10) not null,cid int,foreign key (cid) references class (id)); create table class (id int primary key auto_increment,cname varchar (15)) Add: in Mysql, if the storage engine is not innodb, then the constraint effect of foreign keys cannot be made to take effect, even if foreign keys can be successfully added. Foreign key name can not be repeated, so it is not recommended to use constraint foreign key name foreign key modification and deletion: modify: can not modify foreign key information, such as foreign key pointing and so on, can only be deleted before adding. Delete syntax: alter table table name drop foreign key foreign key name; the foreign key name here is not a foreign key field, but a foreign key name. If you do not use constraint to define it, you can use show create to view the system-defined foreign key name in the table creation statement. Add: when you delete a foreign key, if you use desc, you will see the table structure and MUL, which is an index. Because when you create a foreign key, the field is created as an index. If you don't want to keep it, you can use the drop index field name on table name. Constraint mode of foreign keys: foreign keys are used to constrain the relationship between tables. (it is agreed that the table that creates the foreign key is called the child table, and the table that points to is called the parent table.) for the child table: you can constrain the insertion and modification of the child table [this constraint is the constraint of the parent table on the child table] when it involves the insertion and modification of the foreign key, if the foreign key field cannot find a corresponding match, then the insert / modification will fail (such as inserting a course record is impossible to insert a course that is not in the course schedule). For example: for the parent table: you can constrain the deletion and update of the parent table, and there are usually the following constraint modes. [this constraint is the constraint of the child table on the parent table] pattern: strict strict mode: when it comes to the deletion and update of foreign keys, if the primary key data of the corresponding record is already used by the child table, it cannot be deleted (such as someone has enrolled in a class, the school can not be stupid to delete a class, only those classes that no one is enrolled in can be deleted. ) cascade cascading mode: when it comes to deleting and updating foreign keys, if the field is already used by the subtable, the data in the subtable will be updated accordingly (for example, if a class changes the class number, then the class in the student table will be changed accordingly. If a class is deleted, delete all students in the corresponding class) set null null mode: when it comes to the deletion and update of foreign keys, if the field is already used by the child table, then the foreign key data in the child table will be empty (for example, a class has been deleted, all students should not be deleted) Instead, they should be left blank and then reassigned) [the subtable is allowed to be empty if the field is allowed to be empty] actually different modes can be assigned to different operations (according to my examples), in fact, the appropriate measure is to leave empty when deleting (even if a class is too rubbish and you want to delete a class, you should not drop all students from school, but assign them to another class) Cascading when modifying (class number is allowed to be changed, and the change will be updated to students) setting methods for different modes for different operations (operation in child tables): foreign key (foreign key field) references parent table (primary key) on operation mode foreign key (foreign key field) references parent table (primary key) on delete set null on update cascade -- Experimental table structure create table class (id int primary key auto_increment,cname varchar (15)); create table student2 (id int primary key auto_increment,name varchar (15) not null,gender varchar (10) not null,cid int,foreign key (cid) references class (id) on delete set null on update cascade);-- Experimental table data: insert into class (cname) values ("python"), ("linux"), ("java"), ("html5") Insert into student2 (name,gender,cid) values ("Alice", "female", 1); insert into student2 (name,gender,cid) values ("John", "female", 2); insert into student2 (name,gender,cid) values ("Jack", "female", 3); insert into student2 (name,gender,cid) values ("Amy", "female", 4); select * from student2;select * from class;-- attempt to update cascading update class set id = 6 where cname= "python"; select * from student2 -- as a result, the cid=6-- of the original python tried to delete the empty delete from class where cname= "java"; select * from student2;-- as a result, the cid=null of the original java added: you need to set the constraint mode, and do not use different constraint modes in multiple child tables, otherwise there will be conflicts. The above is about how to use mysql foreign keys, if you have learned knowledge or skills, you can share it for more people to see.

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.