MySQL表的约束

📟作者主页：慢热的陕西人

🌴专栏链接：MySQL

📣欢迎各位大佬👍点赞🔥关注🚓收藏，🍉留言

本博客主要内容介绍了mysql中约束的概念

MySQL表的约束

1.表的约束的概念

表的约束（Database Table Constraints）是数据库管理系统中用来确保数据完整性的规则和限制。它们用于限制存储在表中的数据类型，以确保数据的准确性和可靠性。常见的表约束类型包括：

1. 主键约束（Primary Key Constraint）：确保表中每一行都有唯一的标识符。每个表只能有一个主键，主键列中的值必须是唯一的，并且不能包含 NULL 值。
2. 外键约束（Foreign Key Constraint）：用于在两个表之间建立关系，确保引用的表中的数据在主表中存在。这有助于维护数据的引用完整性。
3. 唯一约束（Unique Constraint）：确保表中的所有行在指定的列或列组合中都有唯一的值。与主键不同，唯一约束可以接受 NULL 值。
4. 检查约束（Check Constraint）：确保列中的数据满足指定的条件。例如，可以用检查约束来确保年龄列的值不小于18。
5. 默认值约束（Default Constraint）：为表中的列指定默认值。如果在插入行时没有为该列提供值，则会自动插入默认值。
6. 非空约束（NOT NULL Constraint）：确保列中的每一行都必须有值，换句话说，该列不能包含 NULL 值。

这些约束共同作用，保证了数据库中的数据是有效的、一致的，并满足业务规则的需要。

2.非空约束

2.1空属性

MySQL 中的空属性

MySQL 中的空属性表示一个字段没有明确的值。它与 NULL 不同，NULL 表示一个未知或不存在的值。

示例：

CREATE TABLE table_name (
  id INT NOT NULL,
  name VARCHAR(255),
  address VARCHAR(255) DEFAULT NULL
);

在这个例子中，name 字段允许空属性，而 address 字段通过 DEFAULT NULL 设置为默认为空属性，id设置为不能为空。

查询空属性：

可以使用 IS NULL 或 IS NOT NULL 运算符来查询空属性：

SELECT * FROM table_name WHERE name IS NULL;

插入空属性：

可以使用 NULL 关键字插入空属性：

INSERT INTO table_name (id, name, address) VALUES (1, NULL, NULL);

其中空属性意味着，我们表中的某些数据可以缺省的表示为NULL,但是有些必须要确定的信息，我们一般设置为NOT NULL。

2.2默认值

默认值：某一种数据会经常性的出现某一个值，可以在一开始就指定好，在需要真实的数据的时候，用户可以选择性的选择默认的值。

mysql> create table if not exists t1(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 18,
    -> gender char(1) default '男');
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 18      |       |
| gender | char(1)             | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into t1 (name, age, gender) values ('张三', 19, '女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (name) values ('李四');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 女     |
| 李四   |   18 | 男     |
+--------+------+--------+
2 rows in set (0.00 sec)

其中默认值和NOT NULL可以同时生效：

无默认值 + NOT NULL	插入数据必须对该列显式的插入数据
有默认值 + NOT NULL	插入数据的时候可以选择缺省，但是不能插入NULL
无默认值 + NULL	插入数据的时候不可以缺省，但是可以选择插入NULL
有默认值 + NULL	插入数据的时候可以选择缺省，也可以选择插入NULL

当我们建表的时候不设定NULL或者默认值，一般sql会设定为default NULL;

当我们设定NOT NULL的时候，sql不会帮我们设定一个默认的default;

3.列描述comment

概念：comment,没有实际的含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或者DBA来了解。

mysql> create table if not exists t2(
    -> name varchar(20) not null comment '用户的姓名',
    -> age tinyint unsigned default comment '用户的年龄'
    -> );
--注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空

通过desc查看不到注释信息：

mysql> desc t2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+

查看创建语句：

mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `name` varchar(20) NOT NULL COMMENT '用户的姓名',
  `age` tinyint(3) unsigned DEFAULT '0' COMMENT '用户的年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4.zerofill

行为：

• 将数字值填充前导零，以达到指定的显示宽度。
• 仅影响值的显示，而不影响其存储或计算。
• 如果值比指定的显示宽度长，则不会将其截断。
• 不适用于负值。

语法：

CREATE TABLE table_name (
  column_name data_type(display_width) ZEROFILL
);

其中：

• data_type 是数字数据类型（INT、BIGINT、DECIMAL 或 NUMERIC）。
• display_width 指定值的显示宽度。

示例：

考虑以下表：

CREATE TABLE numbers (
  id INT(5) ZEROFILL
);

向该表插入以下值：

INSERT INTO numbers (id) VALUES (5), (123), (12345);

表中存储的值如下：

| id |
|—|—|
| 00005 |
| 00123 |
| 12345 |

用途：

ZEROFILL 属性可用于以下用途：

• 确保数字在显示或存储时具有统一的宽度，这在对齐数据或创建格式化报告时很有用。
• 方便比较字符串表示的数字，因为前导零可以使值按字典顺序排列。

注意：

• ZEROFILL 仅适用于数字数据类型。
• 如果未指定显示宽度，则 MySQL 将使用列的默认宽度。
• ZEROFILL 不影响值的排序或聚合。

5.主键

MySQL中的主键约束是一种数据库表级约束，用于唯一标识表中的每一行。这种约束有以下几个重要特性：

1. 唯一性：主键中的值必须是唯一的。这意味着两行不能有相同的主键值。

2. 非空性：主键字段不能为NULL。这是因为主键需要一个实际的值来唯一标识一行。

3. 自动索引：在MySQL中，当你指定一个字段为主键时，系统会自动为该字段创建一个唯一索引。这有助于快速访问和检索数据，因为索引允许数据库软件快速找到具有特定主键值的行。

4. 限制：一个表只能有一个主键。然而，主键可以包含多个字段，这称为复合主键。复合主键意味着两个或更多的字段联合起来唯一标识表中的一行。

5. 自动递增：通常，主键字段被设置为自动递增，尤其是当主键是一个数字时。这意味着每当添加新行时，主键字段的值会自动递增。这是通过使用AUTO_INCREMENT属性实现的，确保了每个记录的唯一性而无需手动输入。

在MySQL中定义主键的一个示例语句如下：

CREATE TABLE example_table (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

在这个例子中，id字段被定义为主键，具有AUTO_INCREMENT属性，保证了每次插入新记录时id都是唯一的。

主键约束是数据库设计中的一个基本元素，它确保了数据的完整性和唯一性。它对于关系型数据库来说尤其重要，因为它们经常依赖主键来执行快速数据检索和表之间的关联。

添加或删除主键：

在已经创建好的表中，你可以使用ALTER TABLE语句来删除现有的主键约束和添加新的主键约束。这些操作在数据库管理中是常见的，尤其是在调整数据库结构以满足应用需求变化时。

删除主键

要从MySQL表中删除主键约束，可以使用以下语句：

ALTER TABLE your_table_name DROP PRIMARY KEY;

这条命令会从名为your_table_name的表中删除现有的主键约束。需要注意的是，如果主键有AUTO_INCREMENT属性，你可能需要先删除该属性，因为直接删除主键可能会遇到错误。

添加主键

向表中添加主键约束，使用以下语句：

ALTER TABLE your_table_name ADD PRIMARY KEY (your_column_name);

如果你想要创建一个复合主键（即多列共同作为主键），可以在括号中包含多个列名，用逗号分隔：

ALTER TABLE your_table_name ADD PRIMARY KEY (column1, column2);

这会将column1和column2设置为联合主键。

示例

假设有一个students表，包含student_id和email两列，你想要删除现有的主键并将email设置为新的主键。你可以按照以下步骤操作：

1. 删除现有的主键（假设是student_id）:

ALTER TABLE students DROP PRIMARY KEY;

2. 将email列设置为新的主键:

ALTER TABLE students ADD PRIMARY KEY (email);

在执行这些操作时，需要确保将成为新主键的列满足主键的所有要求，即它们必须是唯一的且不包含NULL值。

注意事项

• 在进行这类操作之前，确保对数据库进行备份，以避免数据丢失。
• 修改主键会影响到依赖该主键的外键约束和索引，因此需要仔细考虑这些改动对数据库整体结构和性能的影响。
• 如果你的表很大，修改主键操作可能会耗费较长时间，因为MySQL需要重新构建索引。在执行这些操作时，请考虑到可能的性能影响。

复合主键：

假设你正在设计一个数据库来存储学生选课信息，其中每个学生可以选择多门课程，而一门课程也可以被多个学生选择。为了唯一标识每个学生和课程之间的关系，你需要使用到复合主键。复合主键由两个或多个列组成，这些列的组合值在表中必须是唯一的。

创建表并设置复合主键

下面是一个创建名为student_courses的表的例子，其中student_id和course_id两个字段联合作为复合主键：

CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

在这个例子中：

• student_id列代表学生的唯一标识符。
• course_id列代表课程的唯一标识符。
• enrollment_date列记录了学生选课的日期。
• PRIMARY KEY (student_id, course_id)定义了一个复合主键，意味着student_id和course_id的组合必须在student_courses表中唯一。

插入数据

向student_courses表插入数据时，需要确保每个student_id和course_id的组合是唯一的。以下是一些插入记录的例子：

INSERT INTO student_courses (student_id, course_id, enrollment_date) VALUES (1, 101, '2024-01-15');
INSERT INTO student_courses (student_id, course_id, enrollment_date) VALUES (1, 102, '2024-02-15');
INSERT INTO student_courses (student_id, course_id, enrollment_date) VALUES (2, 101, '2024-03-15');

在这些例子中，一个学生（例如，student_id为1）可以选多门课程（如course_id为101和102），但同一学生对同一课程的选课记录只能出现一次。这正是通过复合主键约束保证的。

注意事项

使用复合主键时，需要确保组成主键的每个列都不允许NULL值，因为主键的作用是唯一标识表中的每行数据，而NULL值在此上下文中是无意义的。此外，在设计具有复合主键的表时，应该仔细考虑哪些列真正需要包含在主键中，以确保数据的一致性和完整性。

5.1自增长

在MySQL中，自增长（AUTO_INCREMENT）是一个属性，它允许数据库为表中的每一行自动生成一个唯一的数字。只能用于主键列，确保每一行都有一个唯一的标识符。

以下是关于MySQL中自增长的一些关键点：

1. 用途：

   • 通常用于主键列，确保数据的唯一性。
   • 无需手动为每行输入一个值，数据库会自动为你处理。

2. 设置：

   • 当你创建一个新表时，可以在列定义中使用AUTO_INCREMENT属性来指定哪个列应该自增长。

   sql`CREATE TABLE example (  
       id INT NOT NULL AUTO_INCREMENT,  
       name VARCHAR(255),  
       PRIMARY KEY (id)  
   );`
   

   • 在已经存在的表上，你可以使用ALTER TABLE命令来添加一个自增长属性只能添加在主键上。

3. 特性：

   • 每次插入新行时，自增长列的值会自动增加。
   • 默认情况下，自增长列的起始值为1，每次插入新行时增加1。但这些值都可以调整。
   • 自增长列必须是索引（通常是主键）。
   • 一个表只能有一个自增长列。

4. 插入数据：

   • 当插入新行时，如果省略了自增长列的值（或为其指定了NULL），则MySQL会自动为该列生成一个新的值。

   复制代码
   
   sql`INSERT INTO example (name) VALUES ('Alice');`
   

   • 如果为自增长列指定了一个值，MySQL会检查该值是否唯一，并且大于当前的最大值。如果满足条件，MySQL会使用该值；否则，它会生成一个新的值。

5. 重置和修改：

   • 你可以使用ALTER TABLE命令来重置自增长列的当前值。

   复制代码
   
   sql`ALTER TABLE example AUTO_INCREMENT = 100;`
   

   • 这会将下一个自增长值设置为100。

6. 注意事项：

   • 在某些情况下，手动插入特定的自增长值可能会导致数据冲突或不一致，因此应谨慎操作。
   • 如果删除了表中的行，自增长计数器不会重置。如果你希望重置它，需要手动执行ALTER TABLE命令。

自增长属性在MySQL中是一个非常有用的功能，它简化了数据插入的过程，并确保每行都有一个唯一的标识符。然而，在使用时仍需要注意一些潜在的问题和限制。

6.唯一键

在 MySQL 中，唯一键（Unique Key）是一个非常重要的概念，它用于保证表中某一列或者多列组合的值是唯一的，以确保数据的准确性和完整性。

定义与特性

• 唯一性保证：唯一键约束确保了表中的每条记录在指定的列（或列的组合）上具有唯一的值。这意味着在这些列上，不允许有重复的值出现。
• 允许NULL值：唯一键允许有NULL值，这是它与主键的一个主要区别。标准SQL中，唯一约束允许每个唯一列有多个NULL值（即，你可以有多条记录其唯一键列为NULL，这在逻辑上被视为各不相同），但实际的实现可能依数据库具体而异。
• 多个唯一键：一个表可以有多个唯一键约束，这意味着可以有多组列需要保持其值的唯一性。
• 自动创建索引：当你为表定义一个唯一键时，MySQL 会自动为该唯一键创建一个唯一索引，以加快查找和排序的速度，同时确保数据的唯一性。

应用场景

• 业务逻辑的唯一性要求：在实际应用中，唯一键通常用于那些需要唯一性但不是主键的场景，如邮箱地址、身份证号码等。这些字段在业务逻辑上需要保证唯一，以防止数据重复或错误。
• 数据完整性和准确性：通过使用唯一键，开发人员可以确保数据库中的数据既符合业务规则，又保持了数据的准确性和完整性。

创建唯一键

创建唯一键的语法通常如下：

ALTER TABLE table_name
ADD UNIQUE (column_name);

或在创建表时直接指定：

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

如果是多列组合的唯一性约束，则：

ALTER TABLE table_name
ADD UNIQUE (column1, column2, ...);

或在创建表时：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    UNIQUE (column1, column2, ...)
);

注意事项

• 性能考虑：虽然唯一键提供了数据唯一性的保障，但每个唯一键也是一个索引，太多的索引会影响插入和更新的性能，因为每次这些操作都需要更新索引。
• 设计原则：合理使用唯一键，确保数据的完整性和业务逻辑的需要，同时避免过度设计，以免影响数据库性能。

综上所述，唯一键在数据库设计中是确保数据唯一性的重要工具，合理使用可以大大提高数据质量和应用性能。

与主键的对比：

对比

• 存在原因：主键是为了唯一标识表中的每一行，而唯一键是为了防止特定列（或列的组合）出现重复值。
• NULL 值：主键不允许 NULL 值，而唯一键允许（但具体到实现可能有所不同）。
• 数量限制：每个表只能有一个主键，但可以有多个唯一键。
• 作为外键引用：外键通常引用主键，尽管技术上也可以引用唯一键。

总的来说，虽然主键和唯一键都能确保数据的唯一性，但它们的使用场景和限制有所不同。主键用于唯一标识表中的每条记录，而唯一键则用于保证某一列（或多列）的值在所有记录中是唯一的。

7.外键

MySQL中的外键是一个非常重要的数据库概念，用于确保数据的一致性和完整性。外键是一个表中的字段，它是另一个表的主键的引用。使用外键可以创建两个表之间的关系，这有助于保持数据的一致性和避免数据冗余。

主要特点

• 引用完整性：外键约束确保了一个表中的数据引用另一个表中的有效数据。这意味着，如果表A中有一个字段是表B的外键，那么表A中该字段的每个值都必须在表B的主键字段中存在。
• 防止无效数据：通过使用外键约束，你可以防止在引用的表中添加不存在于主表中的值。
• 级联操作：MySQL支持通过外键约束自动进行级联更新和级联删除。例如，如果你删除了一个记录，那么所有引用该记录的表中的相关记录也可以被自动删除或更新，这取决于外键约束的定义。

使用方式

在创建表或之后添加外键约束的语法如下：

-- 在创建表时定义外键
CREATE TABLE 表名 (
    列名 数据类型,
    ...
    FOREIGN KEY (外键列) REFERENCES 主表(主键列)
);

-- 向现有表添加外键约束
ALTER TABLE 表名
ADD FOREIGN KEY (外键列) REFERENCES 主表(主键列);

注意事项

• 性能影响：虽然外键可以保证数据的一致性，但它们也可能对数据库的性能产生影响。每次插入、更新或删除操作时，数据库都需要检查外键约束，这可能会增加操作的时间。
• 设计选择：在一些情况下，可能会选择不使用外键约束以提高性能，特别是在高并发写入的场景中。这种情况下，应用层需要负责维护数据的一致性。
• 兼容性问题：并不是所有的MySQL存储引擎都支持外键。例如，MyISAM引擎就不支持外键，而InnoDB引擎支持。

在设计数据库时，适当使用外键非常重要，它有助于维护数据的完整性和减少错误数据的产生。然而，也需要考虑到外键的潜在性能影响，并根据具体情况做出合适的设计选择。

到这本篇博客的内容就到此结束了。
如果觉得本篇博客内容对你有所帮助的话，可以点赞，收藏，顺便关注一下！
如果文章内容有错误，欢迎在评论区指正

原文地址：https://blog.csdn.net/weixin_61766635/article/details/137238470

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！