search
HomeDatabaseMysql TutorialMySQL中的外键是什么、有什么作用_MySQL

Mysql外键

本文参加博文大赛,如果您满意的话麻烦点击这里给我投票原微笑,查看原文点击这里.最近自学数据库MySQL,然后有个疑问,一直不得其解,查询了相关资料,最后还是没有解决。
我的疑问是 "使用外键约束" ,然后我对 "外键" 这个词不是很理解,查询相关资料都是讲一些术语,说外键的主要作用是:保持数据的一致性、完整性。听得我是一头雾水。

关于外键,我有自己的一些理解,但是不晓得是否正确,举个例子来表达我的看法:假如现在需要建立一个表,一个什么样的表呢?一个班级的学生个人信息表:

/

所以在设计的时候,就给表1添加一个外键,这个外键就是表2中的学号字段,那么这样表1就是主表,表2就是子表。所以结合2张表就能保持数据的一致性、完整性(估计就是还原成原来的那张大表吧)。
借着这个例子再谈谈外键的一些事项:
1、表1可以有一个或者多个外键,也可以没有。(如果表1有多个外键可不可以是这样的情况,表2中的多个字段是表1的外键;或者说表1的多个外键是在多个表中)
2、这个外键可以不是表1的主键,但必须是子表的主键。(简单的说就是,如果一个字段是某个表的外键时,那么该字段必须是主键)

以上就是我个人对外键的理解。

----------------------------------------解---答---纠---正-----------------------------------------

什么是外键

+-------+ ref +-------+
| sub | ------> | main |
+-------+ +-------+

从表(sub)的某列引用(ref)主表(main)的某列的值。比如学生表有个学生编号(sid),分数表中的学生列(stu)引用学生表的学 生编号,此时对于分数表的 stu 来说,学生表的 sid 就是外键。从表也叫外键表,主表也叫主键表、外表,列也叫字段。

所以在设计的时候,就给表1添加一个外键,这个外键就是表2中的学号字段,那么这样表1就是主表,表2就是子表

你的主从关系理解颠倒了。你的图中,表1的确是主表,表2是子表,但不是叫做给表1添加一个外键,而是给表2添加一个外键,表2中的学号 字段就叫外键,它是表1学号字段的主键。你可以这样说:表1的学号字段是表2的外键。

外键用来干什么

你贴的图片已经解释了。为了一张表记录的数据不要太过冗余。这和软件工程的模块化思想差不多类似,只不过在数据库中是对表关系进行解耦,尽量让表 记录的数据单一化。就如你贴的图片中,把成绩和学生信息放在一张表中就太冗余了,成绩完全可以以学生的id作为区分标识。

为什么说外键能保持数据的一致性、完整性

你想想,你的图中的第一章表分割成了表1和表2,表2的学号引用了表1的学号字段作为外键,如果不建立外键,只是和表1一样单纯性 地设立一个学号字段,那么和建立外键有什么区别呢?

比如表1中张三的学号为20140900001,那么我在表2中插数据的时候在学号字段插20140900001来记录张三的成绩不也是做到了表 的解耦了吗?

这里存在的问题是,在不设置外键的情况下,表2的学号字段和表1的学号字段是没有关联的。只是你自己认为他们有关系而已,数据库并 不认为它俩有关系。也就是说,你在表2的学号字段插了一个值(比如20140999999),但是这个值在表1中并没有,这个时候,数据库还是允 许你插入的,它并不会对插入的数据做关系检查。然而在设置外键的情况下,你插入表2学号字段的值必须要求在表1的学号字段能找到。 同时,如果你要删除表1的某个学号字段,必须保证表2中没有引用该字段值的列,否则就没法删除。这就是所谓的保持数据的一致性和完整性。你想,如 果表2还引用表1的某个学号,你却把表1中的这个学号删了,表2就不知道这个学号对应的学生是哪个学生。数据的一致性还包括数据类型的一致性(这 个见下面就知道了)。

外键的使用规则

从表的字段必须与外键类型相同(如上,分数表 stu 的类型必须和学生表 sid 的类型相同,比如都是 int(10) 类型)外键必须是主表的唯一键(如上,学生表 sid 是主键,而主键是唯一的,所以可以作为分数表 stu 的外键)有关联的字段(如上,分数表之所以使用学生表的 sid 是因为两者有关联,分数表记录的是学生的分数,而学生可以用 sid 来唯 一标识)避免使用复合键(也就是说从表可以同时引用多个外表的字段作为一个外键,一般不推荐这种做法)

你的问题

如果表1有多个外键可不可以是这样的情况,表2中的多个字段是表1的外键;或者说表1的多个外键是在多个表中

都可以。因为表1的外键不一定是表2的主键,也可以是唯一键(UNIQUE)。比如表2有个主键 A,有个唯一键 B,表1两个字段 A' 和 B’ 分别引用表2的 A 和 B,这就是多对多的关系了。再或者表2主键 A,表3主键 B,表1的两个字段 A' 和 B' 分别引用表2的 A 和表3 的 B。

这个外键可以不是表1的主键,但必须是子表的主键。(简单的说就是,如果一个字段是某个表的外键时,那么该字段必须是主键)

因为你前面就理解错了,所以这句话本身就是错的。对于从表来说,外键不一定需要作为从表的主键,外键也不一定是外表的主键,外表的唯一键就可以作 为从表的外键。

再给一张图以帮助理解

/

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools