Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the difference between foreign keys and primary keys in mysql". In daily operation, I believe that many people have doubts about the difference between foreign keys and primary keys in mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "what is the difference between foreign keys and primary keys in mysql?" Next, please follow the editor to study!

In mysql, there are differences between foreign keys and primary keys: 1, the primary key is used to ensure data integrity, and the foreign key is used to establish links with other tables; 2, the primary key can only have one, and a table can have multiple foreign keys; 3, the primary key cannot be duplicated and null is not allowed, while the foreign key can be duplicated and can be null.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

Is there any difference between the foreign key and the primary key in mysql

Both primary and foreign keys are constraints. The primary key is used to maintain data integrity, and the foreign key acts on two tables to establish a connection. The following describes the primary key, foreign key and their differences.

Primary key

There are often multiple columns in a table, and the primary key is the column that uniquely identifies each row in the table. A table can have only one primary key, and the primary key column cannot contain null values.

For example:

Student form (student number, name, gender, class)

The student number of each student is unique, and the student number is a primary key.

Course schedule (course number, course name, credits)

The course number is unique, and the course number is a primary key.

Joint primary key

When a column cannot uniquely identify each row, use a federated primary key to treat the two columns as a whole. Non-empty, unique, non-repetitive.

For example:

Transcript (student number, course number, grade)

A single attribute in the score sheet can not uniquely identify a record, only the combination of the student number and the course number can uniquely identify a record, so the attribute group of the student number and the course number is a primary key.

Foreign key

Foreign keys are used to associate with another table. A table with the foreign key of another table as the primary key is called the master table, and the table with this foreign key is called the slave table of the master table.

The foreign key value of the table must be found in the main table.

When the record of the master table is referenced by the slave table, the record of the master table is not allowed to be deleted.

For example:

The class table is associated with the Student table through class_id to achieve an one-to-many relationship. Class_id is the foreign key of the Student table, the Student table is the slave table, the class table is the primary key, and the class table is the primary table.

The difference between primary key, foreign key and index: a. Define

Primary key: uniquely identifies a record, cannot be duplicated, and is not allowed to be empty.

Foreign key: the foreign key of the table is the primary key of another table. The foreign key can be duplicated and can be null.

Index: this field does not have a duplicate value, but can have a null value.

b. Action

Primary key: used to ensure data integrity

Foreign keys: used to connect with other tables

Index: used to improve the speed of query sorting

c. Number

Primary key: there can be only one primary key.

Foreign keys: a table can have multiple foreign keys.

Index: a table can have multiple unique indexes.

Add primary key, foreign key constraint

Primary key constraint

Create table table name (column name 1 data type primary key, column name 2 data type);-or-create table table name (column name 1 data type, column name 2 data type, primary key (column name 1));-existing table-alter table table name add primary key (column name)

Foreign key constraint

-main table-create table Table 1 (column name 1 data type primary key, column name 2 data type) -slave table (table where foreign key constraints are located)-create table Table 2 (FK_ column name 1 data type, column name 2 data type, constraint foreign key constraint name foreign key (slave table column name 1) references master table name (master table column name 1); at this point, the study on "what is the difference between foreign keys and primary keys in mysql" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.