What is the difference between primary key and foreign key in mysql

The difference between primary key and foreign key: Function: The primary key uniquely identifies the record, and the foreign key associates records in different tables. Uniqueness: The primary key is unique and no duplication is allowed; the foreign key is not unique and can be repeated. NULL values: NULL values ​​are not allowed for primary keys and NULL values ​​are allowed for foreign keys. Relationship: The primary key is the relationship within the table, and the foreign key is the relationship between tables. Constraints: The primary key has non-null and unique constraints, and the foreign key has constraints that reference the primary key of the main table. Quantity: Each table can only have one primary key, and a table can have multiple foreign keys.

The difference between primary key and foreign key in MySQL

Primary key

• A set of columns that uniquely identify each record in each table
• Cannot be empty (non-null constraint)
• And cannot be repeated (unique constraint)
• In MySQL, each table can only have one primary key

Foreign key

• A column or column corresponding to the primary key in another table Group column
• The foreign key column must refer to the primary key that exists in the main table
• The foreign key column can be empty (null values ​​are allowed)
• In MySQL, a table can have multiple Foreign keys

Specific differences

Characteristics	Primary key	Foreign keys Key
Function	Uniquely identifies a record	Associates related records in different tables
Uniqueness	Unique, duplicates are not allowed	Not unique, duplicates are allowed
Null value	Not allowed	Allow
Relationships	Associations within tables	Associations between tables
Constraints	Non-null, unique	Reference the primary key of the main table
Quantity	Only one per table	A table can have multiple

Example

• Primary key example: A table named "Users" whose primary key is the "user_id" column. Each record has a unique "user_id" that can be used to identify the user.
• Foreign key example: A table named "Orders" whose foreign key is the "user_id" column. This key references the "user_id" primary key in the "Users" table, indicating that each order is associated with a specific user.

Function

• Primary key: Ensure the uniqueness of records in the table and optimize the data retrieval speed.
• Foreign keys: Establish relationships between tables, maintain data integrity and prevent orphan records.

The above is the detailed content of What is the difference between primary key and foreign key in mysql. For more information, please follow other related articles on the PHP Chinese website!