一周掌握MySQL（三）：主键、外键、可视化软件

一：主键、外键定义

**主键（Primary Key）和外键（Foreign Key）**是关系数据库中的两个重要概念，用于确保数据的一致性和完整性。

• 主键：是表中用来唯一标识每一行数据的字段（或字段组合）。每个表只能有一个主键，主键的值不能重复，也不能为 NULL。它确保了表中每一行数据都是唯一的。例如，在一个用户表中，用户ID通常会作为主键，因为每个用户都有一个唯一的ID。

• 外键：是一个表中的字段，它引用另一个表的主键。外键用于建立和维护两个表之间的关系，确保引用的值在另一个表中存在。外键可以包含重复值或为空（如果允许的话）。例如，在一个订单表中，用户ID可能作为外键，引用用户表的主键，以表示哪个用户下了订单。

举个例子：

• 在一个 Users 表中，UserID 是主键，因为每个用户都有一个唯一的ID。
• 在一个 Orders 表中，UserID 是外键，它引用 Users 表中的 UserID。这样可以知道每个订单是哪个用户下的。

这样，主键确保了每行数据的唯一性，而外键确保了不同表之间的数据一致性。

二：主键、外键案例

CREATE TABLE UUT_RESULT
(
    ID                      CHAR (38) PRIMARY KEY,
    STATION_ID              VARCHAR (255),
    BATCH_SERIAL_NUMBER     VARCHAR (255),
    TEST_SOCKET_INDEX       INT,
    UUT_SERIAL_NUMBER       VARCHAR (255),
    USER_LOGIN_NAME         VARCHAR (255),
    START_DATE_TIME         DATETIME,
    EXECUTION_TIME          DOUBLE,    
    UUT_STATUS              VARCHAR (32),
    UUT_ERROR_CODE          INT,
    UUT_ERROR_MESSAGE       VARCHAR (255),
    PART_NUMBER             VARCHAR (255),
    TSR_FILE_NAME           VARCHAR (255),
    TSR_FILE_ID             VARCHAR (64),
    TSR_FILE_CLOSED        TINYINT
)
;

# SQL command to create the STEP_RESULT table in an SQL Database.
CREATE TABLE STEP_RESULT
(
    ID                      CHAR (38) PRIMARY KEY,
    UUT_RESULT              CHAR (38) NOT NULL,
    STEP_PARENT             CHAR (38),
    ORDER_NUMBER            INT,
    STEP_NAME               VARCHAR (255),
    STEP_TYPE               VARCHAR (255),
    STEP_GROUP              VARCHAR (32),
    STEP_INDEX              INT,
    STATUS                  VARCHAR (255),
    STEP_ID                 VARCHAR (32),
    REPORT_TEXT             VARCHAR (255),
    ERROR_CODE              INT,
    ERROR_MESSAGE           VARCHAR (255),
    CAUSED_SEQFAIL          TINYINT,
    MODULE_TIME             DOUBLE,
    TOTAL_TIME              DOUBLE,
    NUM_LOOPS               INT,
    NUM_PASSED              INT,
    NUM_FAILED              INT,
    ENDING_LOOP_INDEX       INT,
    LOOP_INDEX              INT,
    INTERACTIVE_EXENUM      INT,
    CONSTRAINT UUT_RESULT_FK FOREIGN KEY (UUT_RESULT) REFERENCES UUT_RESULT (ID)
)
;

在这个例子中，STEP_RESULT 表中的 UUT_RESULT 字段是外键，它引用了 UUT_RESULT 表中的 ID 字段。这个外键约束确保了 STEP_RESULT 表中的 UUT_RESULT 值必须在 UUT_RESULT 表的 ID 字段中存在。这样可以维护两张表之间的参照完整性。

• UUT_RESULT 表：包含主键 ID。
• STEP_RESULT 表：包含外键 UUT_RESULT，它引用了 UUT_RESULT 表的 ID 字段。 

在 SQL 中，STEP_RESULT 表中的 UUT_RESULT 字段引用 UUT_RESULT 表中的 ID 字段的机制通过外键约束来体现。这个约束确保了数据的一致性，即 STEP_RESULT 表中的 UUT_RESULT 字段的值必须存在于 UUT_RESULT 表的 ID 字段中。

具体来说，外键约束的定义如下：

CONSTRAINT UUT_RESULT_FK FOREIGN KEY (UUT_RESULT) REFERENCES UUT_RESULT (ID)

在这条语句中：

• CONSTRAINT UUT_RESULT_FK：定义了一个名为 UUT_RESULT_FK 的外键约束。
• FOREIGN KEY (UUT_RESULT)：指定 STEP_RESULT 表中的 UUT_RESULT 列作为外键。
• REFERENCES UUT_RESULT (ID)：定义了外键引用的目标表和列，这里是 UUT_RESULT 表中的 ID 列。 

 

细说明：

1. 数据完整性：

   • 任何插入到 STEP_RESULT 表中的 UUT_RESULT 字段的值，必须在 UUT_RESULT 表的 ID 列中找到对应的值。如果尝试插入一个不在 UUT_RESULT 表中的 ID 值，数据库会返回错误。

2. 关系维护：

   • 如果 UUT_RESULT 表中的某个 ID 值被删除或更新，数据库可以选择如何处理这些操作。通常有三种处理方式：
     • CASCADE：删除或更新 UUT_RESULT 表中的记录时，会自动删除或更新 STEP_RESULT 表中相关的记录。
     • SET NULL：删除或更新 UUT_RESULT 表中的记录时，STEP_RESULT 表中的相关外键值会被设置为 NULL。
     • RESTRICT 或 NO ACTION：如果 STEP_RESULT 表中存在引用的记录，不能删除或更新 UUT_RESULT 表中的记录。

示例：

假设你有以下数据：

• UUT_RESULT 表：

  • ID: 1234
  • STATION_ID: A1

• STEP_RESULT 表：

  • ID: 5678
  • UUT_RESULT: 1234 (引用了 UUT_RESULT 表中的 ID 值)

在这种情况下，STEP_RESULT 表中的 UUT_RESULT 字段值 1234 必须在 UUT_RESULT 表的 ID 列中存在。这保证了两个表之间的数据关系是一致的。

上面这个解释能帮助我们理解外键约束如何在 SQL 中定义和应用！

 三：可视化工具软件的比较

MySQL Workbench 和 Navicat 都是流行的数据库管理工具，它们各自有不同的特点和优势。以下是它们的一些对比，帮助你根据需求选择合适的工具：

3.1MySQL Workbench

优点：

1. 官方工具：由 MySQL 官方开发，通常与 MySQL 数据库的兼容性最好。
2. 免费：提供免费的 Community 版本，适合大多数基础使用场景。
3. 综合功能：
   • 建模工具：支持数据库设计和建模，可以创建 ER 图。
   • 查询编辑器：功能强大的 SQL 编辑器，支持语法高亮和自动完成。
   • 数据迁移：支持数据库迁移和数据导入导出。
   • 性能监控：内置性能监控和诊断工具。

缺点：

1. 用户界面：对于新手用户，可能会感觉界面稍显复杂和繁琐。
2. 跨平台性能：在不同操作系统上的表现可能不如某些专门的跨平台工具流畅。
3. 功能限制：某些高级功能可能需要额外的付费插件或工具。

3.2Navicat

优点：

1. 用户友好：界面现代化，易于使用，适合各种用户，特别是新手。
2. 多数据库支持：除了 MySQL，还支持多种数据库系统，如 PostgreSQL、Oracle、SQLite、SQL Server 等。
3. 全面功能：
   • 数据同步：提供强大的数据同步和备份工具。
   • 可视化工具：支持数据库设计、SQL 生成、数据建模和报表功能。
   • 智能工具：包括智能数据编辑、数据导入导出和批量操作功能。

缺点：

1. 收费：Navicat 是商业软件，虽然有试用版，但长期使用需要购买许可证。
2. 资源占用：在某些情况下，Navicat 可能比 MySQL Workbench 占用更多的系统资源。
3. 更新频率：更新和新功能发布的频率可能不如 MySQL Workbench。

3.3总结

• 选择 MySQL Workbench：如果你主要使用 MySQL 数据库，并且希望使用一个免费的官方工具，MySQL Workbench 是一个不错的选择。它提供了强大的数据库设计和管理功能。

• 选择 Navicat：如果你需要支持多种数据库系统，或者更喜欢用户友好且功能丰富的商业工具，Navicat 可能更适合你。尽管它需要付费，但其全面的功能和优雅的用户界面可以提高工作效率。