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

MySQL Foreign key of Python full Stack Road Series

Let's give an example to illustrate what foreign keys are and the role of foreign keys. So,XO is now in a state of rapid development, from the initial 12-member team to the current 300. then the problem arises. The faster it develops, there will be more and more personnel and departments. This is the boss's request that we do a personnel management system to query the information of recruits, and so on.

At first, we wanted to use a table to implement all the personnel statistics and create a Personnel library to store the information of the company's employees, with the following instructions:

CREATE DATABASE personnel DEFAULT CHARSET utf8 COLLATE utf8_general_ci

Then create a personnel information table in which the id and name columns are the joint primary keys, the engine is InnoDB, and the character set is in utf8 format in the person_ info table

CREATE TABLE `person_ info` (--personnel ID `id` int (11) NOT NULL AUTO_INCREMENT,-- name `name` varchar (10) NOT NULL,-- email `email` varchar (15) DEFAULT NULL,-- Mobile phone number `phone` char (11) NOT NULL,-- Department `department` varchar (32) DEFAULT NULL,-- position `position` varchar (30) DEFAULT NULL,-description `caption` varchar (255) DEFAULT NULL, PRIMARY KEY (`id`) `name`) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Insert member data

INSERT INTO person_info (name,email,phone,department,position,caption) VALUES ('axianshengme.com,' 13800138000developers,'XO Co., Ltd.-Technology Department-Python Development', 'Python API Development','I am an Python development engineer specializing in PYthon API development') INSERT INTO person_info (name,email,phone,department,position) VALUES ('baked,' breadanshengme.com, '13800138000,' XO Co., Ltd.-Technology Department-JAVA Development', 'JAVA APP Development'); INSERT INTO person_info (name,email,phone,department,position) VALUES ('name,email,phone,department,position,' cateranshengme.com, '13800138000,' XO Co., Ltd.-Technology Department-Front end', 'JavaScript') INSERT INTO person_info (name,email,phone,department,position) VALUES, '13800138000,' XO Co., Ltd.-Technology Department-DBA', 'MySQL DBA'); INSERT INTO person_info (name,email,phone,department,position) VALUES (' eBay, 'estranganshengme.com,' 13800138000,'XO Co., Ltd.-Technology Department-Server Group, 'Linux')

View inserted data

Mysql > select * from person_info +-+ -+ | id | name | email | phone | department | position | caption | | + -+-+ | 1 | a | a@anshengme.com | 13800138000 | XO Co., Ltd.-Technical Department-Python Development | Python API Development | I am a Python development engineer Focus on PYthon API development | | 2 | b | b@anshengme.com | 13800138000 | XO Co., Ltd.-Technical Department-JAVA Development | JAVA APP Development | NULL | | 3 | c | c@anshengme.com | 13800138000 | XO Co., Ltd.-Technical Department-Front End | JavaScript | NULL | | 4 | d | d@anshengme.com | 13800138000 | XO-Technical Department-DBA | MySQL DBA | NULL | | 5 | e | e@anshengme.com | 13800138000 | XO Co., Ltd.-Technical Department-Server Group | Linux | NULL | + -- + -+ 5 rows in set (0.00 sec)

So, we just inserted the above five pieces of data, so if there are thousands of pieces of data, is the department duplicated? Because individuals must belong to a department, may also belong to a group under this department, and the above department field occupies only a few characters, if there is a perverted boss, it is necessary to make the famous cars of the department very long, so does this take up a lot of space? And the query is not that fast.

So, the role of foreign keys came out, so now the table design has changed, what does it look like?

The person_ info table is unpacked into two tables, which are divided into part department table and person_info personnel information table.

Part table has nid and caption columns, nid as the primary key and self-increment, caption stores all the departments of the company, and so on.

Person_info is still a personnel information table, with the same fields as the above table, id and name are still primary keys, and id is self-increasing. The only change is that the part_nid column is associated with the nid column in the part table. The table design is as follows:

Delete the previously created person_ info table first

DROP TABLE person_info

Create a part job sheet

CREATE TABLE part (nid int (11) NOT NULL AUTO_INCREMENT, caption varchar (32) NOT NULL, PRIMARY KEY (nid) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert department data

INSERT INTO part (caption) VALUES ("XO Corporation-Technology Department-JAVA Development"), ("XO Co., Ltd.-Technology Department-Front end"), ("XO Co., Ltd.-Technology Department-DBA"), ("XO Co., Ltd.-Technology Department-Server Group"), ("XO Co., Ltd.-Technology Department-Python Development")

View inserted department information

Mysql > select * from personnel.part +-+-+ | nid | caption | +-+- -+ | 1 | XO Co., Ltd-Technology Department-JAVA Development | | 2 | XO Co., Ltd.-Technology Department-Front end | | 3 | XO Co., Ltd.-Technology Department-DBA | | 4 | XO Co., Ltd. Company-Technology Department-Server Group | | 5 | XO Co., Ltd.-Technology Department-Python Development | +-+-- + 5 rows in set (0.00 sec)

Create a person_info personnel information table

CREATE TABLE `person_ info` (`nid` int (11) NOT NULL AUTO_INCREMENT, `name` char (10) NOT NULL, `email` varchar (20) NOT NULL, `phone` char (11) NOT NULL, `part_ nid` int (11) NOT NULL, `position` char (20) NOT NULL, `caption` varchar (255) DEFAULT NULL, PRIMARY KEY (`nid`, `name`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Set foreign key

Make a foreign key association between the part_ Nid column in the person_ info table and the nid column in the part table. The name of the foreign key is person_ibfk_1.

Alter table person_info add constraint person_ibfk_1 foreign key person_info (`part_ nid`) REFERENCES part (`nid`)

Delete foreign key

Delete the person_ibfk_1 foreign key in the person_ info table

Alter table person_info drop foreign key person_ibfk_1

Insert 6 pieces of data into the member table

INSERT INTO person_info (NAME, email, phone, part_nid, position) VALUES ("as", "as@anshengme.com", 13800138000,5, "Python"), ("ansheng", "as@anshengme.com", 13800138000,5, "Python"), ("a") "as@anshengme.com", 13800138000,5, "Python"), ("v", "as@anshengme.com", 13800138000,5, "Python"), ("b", "as@anshengme.com", 13800138000,5, "Python") ("w", "as@anshengme.com", 13800138000, 5, "Python")

The data inserted above is legal, so what if we insert illegal data? For example, what happens if you insert a department that doesn't exist in the part table?

Mysql > use personnel;Database changedmysql > INSERT INTO person_info (name,email,phone,part_nid,position) VALUES ("pwd", "pwd@anshengme.com", 13800138000 VALUES 10, "Python"); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`personnel`.`person _ info`, CONSTRAINT `person_ibfk_ 1`FOREIGN KEY (`part_ nid`) REFERENCES `part` (`nid`))

So, did you just report it wrong? No, this is the constraint of the foreign key. If the data you insert, the data in the part_nid column is not in the nid column of the part table, then it is absolutely impossible.

The above is an one-to-many relationship, and many-to-many and linked table queries will be introduced in the next article, which is actually very simple.

# Python full Stack Road

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.