先来看看一个mysql中desc xxx表结果解释:

mysql 主键重复就覆盖_字段

  • “Field” 列显示了表的每个字段(即列)的名称。
  • “Type” 列显示了每个字段的数据类型。
  • “Null” 列指示该字段是否可以为 NULL。如果该字段不允许为 NULL,则此列显示 “NO”;否则,显示 “YES”。
  • “Key” 列显示了该字段是否被定义为某种键。“PRI” 表示该字段是表的主键。
  • “Default” 列显示了该字段的默认值。在这个例子中,所有字段的默认值都是 NULL。
  • “Extra” 列显示了任何额外的信息。在这个例子中,没有字段有额外的信息。

下面进入正题。


文章目录

  • MySQL8 数据库表中字段约束
  • 引言
  • 主键约束 (PRIMARY KEY)(每个表只能有一个主键,而且主键的值必须是唯一的,并且不能是 NULL;主键可使用单一字段,也可使用两个或更多字段组合成复合主键)
  • 关于表的主键(Primary Key)主键约束
  • 关于复合主键(Composite Primary Key)复合主键约束
  • 唯一约束 (UNIQUE)(保证字段数据的唯一性;一个表可以存在多个唯一约束)
  • 主键约束与唯一约束的区别
  • 非空约束 (NOT NULL)(确保表中的某列(某个字段)不能有 NULL 值)
  • 默认约束 (DEFAULT)(如果插入数据没有为某列(某个字段)指定值,则会使用默认值)
  • 设置默认约束同时设置非空约束
  • 检查约束 (CHECK)(用于限制某列(某个字段)值的范围)
  • 设置了检查约束不代表默认设置了非空约束
  • 注意:MySQL 8.0.16 版本以前不支持检查约束
  • 外键约束 (FOREIGN KEY)(与其他表)
  • 外键约束常见用法
  • 1. 确保数据完整性
  • 2. 创建关系
  • 示例
  • 3. 级联操作(决定当更改或删除一个被外键引用的主键值时,系统如何自动更新或删除相关的外键值)
  • 级联操作拓展


MySQL8 数据库表中字段约束

引言

在构建一个数据库模型时,一项重要的任务是确保数据的完整性和准确性。为了实现这个目标,MySQL 提供了多种约束类型来限制对表中数据的修改。这些约束包括主键约束、唯一约束、非空约束、默认约束、检查约束和外键约束。


主键约束 (PRIMARY KEY)(每个表只能有一个主键,而且主键的值必须是唯一的,并且不能是 NULL;主键可使用单一字段,也可使用两个或更多字段组合成复合主键)

主键约束定义了表中数据的唯一标识符。每个表只能有一个主键,而且主键的值必须是唯一的,并且不能是 NULL。

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

在这个例子中,IDEmployees 表的主键。

关于表的主键(Primary Key)主键约束

表的主键 (Primary Key) 是一个或多个字段(列),它们唯一地标识了数据库表中的每条记录。主键的重要性质是它必须包含唯一的值,并且不能为 NULL。在数据库设计中,选择适当的主键非常重要,因为主键不仅用于唯一标识记录,而且经常被其他表作为外键引用。

例如,假设有一个名为 “Employees” 的表,其中包含 “ID”, “LastName”, “FirstName” 和 “Age” 四个字段。“ID” 字段可以被设定为主键,因为每个员工都有一个独特的 ID。

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

在这个例子中,ID 列是主键,这意味着它必须包含唯一的值(即每个员工的 ID 必须不同),并且不能为 NULL(即每个员工都必须有一个 ID)。

注意:作为主键的字段是自动加上NOT NULL约束的,不写mysql也会帮你自动加上

关于复合主键(Composite Primary Key)复合主键约束

除了使用单一字段作为主键,也可以使用两个或更多字段组合成为一个复合主键(Composite Primary Key)。这种情况常见于需要使用两个或更多字段来唯一标识记录的场景。

例如,在一个订单明细表中,可能存在多个订单ID对应多个产品ID的情况。此时,可以将 OrderIDProductID 两列组合作为主键。

CREATE TABLE OrderDetails (
    OrderID int NOT NULL,
    ProductID int NOT NULL,
    Quantity int,
    PRIMARY KEY (OrderID, ProductID)
);

在这个例子中,OrderIDProductID 组合成了一个复合主键。这意味着任何两条记录不能同时具有相同的 OrderIDProductID


唯一约束 (UNIQUE)(保证字段数据的唯一性;一个表可以存在多个唯一约束)

唯一约束保证了表中的所有数据都具有独特的值。一个表可以有多个唯一约束,但每个唯一约束都必须有一个不同的值。

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

在这个例子中,ID 列有一个唯一约束,所以任何两行都不能有相同的 ID 值。

主键约束与唯一约束的区别

主键约束和唯一约束确实有一些相似之处,但也存在一些重要的区别:

  1. NULL值:主键字段不允许插入 NULL 值,而唯一约束的字段是可以接受 NULL 值的(除非显式地声明为 NOT NULL)。
  2. 数量:在一个表中,只能有一个主键(尽管这个主键可以由多个字段组成),而一个表可以有多个唯一约束。
  3. 用途:主键通常是表的记录被其他表引用的方式(即外键)。而唯一约束则仅确保某个字段的值唯一。

下面是对两者进行展示的例子:

CREATE TABLE Example (
    ID int NOT NULL,
    Value int,
    PRIMARY KEY (ID),
    UNIQUE (Value)
);

在这个例子中,ID 是主键,不能为 NULL,并且必须是唯一的。Value 有一个唯一约束,所以它的值也必须是唯一的,但是可以为 NULL。


非空约束 (NOT NULL)(确保表中的某列(某个字段)不能有 NULL 值)

非空约束确保表中的某列不能有 NULL 值。

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
);

在这个例子中,IDLastName 列都有非空约束,所以它们不能接受 NULL 值。


默认约束 (DEFAULT)(如果插入数据没有为某列(某个字段)指定值,则会使用默认值)

默认约束用于向列中插入默认值。如果没有为该列指定值,则会使用默认值。

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int DEFAULT 30,
);

在这个例子中,如果在插入新行时没有指定 Age 值,则会使用默认值 30。

设置默认约束同时设置非空约束

注意:设置默认约束时,也是可以同时设置非空约束的。如:

Age int NOT NULL DEFAULT 30;

检查约束 (CHECK)(用于限制某列(某个字段)值的范围)

检查约束用于限制列中的值的范围。如果尝试插入违反检查约束的值,那么 MySQL 将拒绝这个操作。

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age >= 18)
);

在这个例子中,Age 列有一个检查约束,它要求所有员工的年龄必须大于或等于 18。

设置了检查约束不代表默认设置了非空约束

如上例子,虽然 CHECK 约束确保了 Age 字段的值必须大于或等于 18,但它并没有明确禁止 NULL 值。

在 SQL 标准中,NULL 是一个特殊的值,它表示“未知”或“不适用”。当对 NULL 值进行比较时,结果总是未知(也就是说,NULL 既不大于、小于也不等于任何值)。因此,如果 Age 字段包含 NULL,那么 CHECK (Age >= 18) 约束将无法确定是否满足条件,所以它会允许 NULL 值。

如果想确保 Age 字段既不能为 NULL,又必须大于或等于 18,可以这样设置:

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int NOT NULL CHECK (Age >= 18)
);

在这个例子中,NOT NULL 约束确保 Age 字段不能为 NULL,而 CHECK 约束则确保 Age 字段的值必须大于或等于 18。

注意:MySQL 8.0.16 版本以前不支持检查约束

在 MySQL 8.0.16 版本以前,尽管可以在语法中包含 CHECK 约束,但 MySQL 并不真正地对其进行检查。也就是说,即使数据违反了 CHECK 约束,MySQL 也会允许这些数据被插入或更新到表中。

从 MySQL 8.0.16 版本开始,MySQL 开始支持 CHECK 约束,并会在数据违反约束时抛出错误。

例如,假设你在 MySQL 8.0.15 或更早的版本上运行以下命令:

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age >= 18)
);

INSERT INTO Employees (ID, LastName, FirstName, Age)
VALUES (1, 'Doe', 'John', 17);

虽然 17 小于 CHECK 约束所规定的最小年龄 18,但 MySQL 仍会允许这条 INSERT 语句执行成功。

然而,如果你在 MySQL 8.0.16 或更高版本上运行同样的命令,MySQL 会因为 Age 的值违反了 CHECK 约束而拒绝执行 INSERT 语句,并抛出一个错误。

请注意,在编写涉及 CHECK 约束的代码时,应考虑到正在使用的 MySQL 版本,以确保代码的行为符合预期。

吓得我赶紧看了下我的mysql版本:

8.0.34,还好。

mysql 主键重复就覆盖_mysql 主键重复就覆盖_02


外键约束 (FOREIGN KEY)(与其他表)

外键约束用于防止对表进行破坏关系数据完整性的操作。一个表可以有多个外键。

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    EmployeeID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);

在这个例子中,EmployeeIDOrders 表的外键,它引用了 Employees 表的 ID 列。如果尝试插入一个不存在于 EmployeesID 列中的值,或者删除 Employees 表中某个被 Orders 表引用的 ID,MySQL 将拒绝这个操作。

外键约束常见用法

外键(Foreign Key)是数据库中的一个重要概念,用于链接两个表,并对其中一个表中的数据进行约束。外键在一个表中定义,它引用另一个表的主键。

以下是一些常见的外键约束用法:

1. 确保数据完整性

通过使用外键约束,你可以确保在两个相关联的表之间维护数据完整性。如果尝试插入一个不存在的值或删除一个被其他记录引用的值,数据库将抛出错误。

2. 创建关系

外键允许你创建表与表之间的关系。例如,你可能有一个 Orders 表和一个 Customers 表,其中 Orders 表中的 CustomerID 列是 Customers 表的 ID 列的外键。这样就创建了从订单到顾客的关系。

示例

以下是如何创建 Customers 表和 Orders 表并在它们之间建立关系的示例:

首先,我们创建一个 Customers 表,其中有两个字段:IDName。其中 ID 是主键。

CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255),
    PRIMARY KEY (ID)
);

接下来,我们创建一个 Orders 表,其中有三个字段:OrderID, ProductCustomerID。其中 OrderID 是主键,CustomerID 是外键,引用了 Customers 表的 ID 字段。

CREATE TABLE Orders (
    OrderID int NOT NULL,
    Product varchar(255),
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
);

现在,我们有了两个表,而且它们之间通过 CustomerID 字段(Orders 表中的外键)和 ID 字段(Customers 表中的主键)建立了关联。这意味着每个订单都可以追溯到一个顾客。同时,如果尝试输入一个不存在于 Customers 表中的 CustomerID,或者删除一个正在被 Orders 表引用的 Customers 表记录,数据库将抛出错误,以保持数据的完整性。

3. 级联操作(决定当更改或删除一个被外键引用的主键值时,系统如何自动更新或删除相关的外键值)

当你在定义外键时,可以指定级联操作(如 CASCADE, SET NULL, SET DEFAULT, NO ACTION, RESTRICT)。这决定了当你更改或删除一个被外键引用的主键值时,系统如何自动更新或删除相关的外键值。

例如,假设我们有两个表:OrdersCustomers。我们可以在 Orders 表中设置一个外键约束,使 CustomerID 引用 Customers 表的 ID 列,并且当 Customers 表中的一条记录被删除时,删除 Orders 表中所有相关的订单:

CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255),
    PRIMARY KEY (ID)
);

CREATE TABLE Orders (
    OrderID int NOT NULL,
    CustomerID int,
    Product varchar(255),
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(ID) ON DELETE CASCADE
);

在这个例子中,如果你删除了一个顾客,那么所有该顾客的订单也将被自动删除,以保持数据的一致性。

级联操作拓展

(待更)