MySQL约束笔记

约束

在MySQL中，约束（Constraints）用于定义和强制数据表中的规则，以确保数据的完整性和一致性。以下是MySQL中常用的约束类型：

• 1. 主键约束（Primary Key Constraint）：
     主键约束用于标识表中的唯一记录。一个表只能有一个主键，且主键值不能为NULL。常用于在表中唯一标识每条记录。
     例如：

     CREATE TABLE table_name (
       id INT PRIMARY KEY,
       name VARCHAR(50)
     );
     

  2. 唯一约束（Unique Constraint）：
     唯一约束用于确保表中的某个列的值是唯一的。一个表可以有多个唯一约束，每个唯一约束可以包含一个或多个列**（唯一性约束，不允许出现一样的，但是可以出现很多NULL值）**。
     例如：

     CREATE TABLE table_name (
       email VARCHAR(100) UNIQUE,
       phone VARCHAR(20) UNIQUE
     );
     

  3. 外键约束（Foreign Key Constraint）：
     外键约束用于在两个表之间建立关联，确保数据的一致性。外键约束可以指定一个或多个列，引用其他表中的主键或唯一键。
     例如：

     CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
     );
     

  4. 非空约束（NOT NULL Constraint）：
     非空约束用于确保表中的某个列的值不能为空。在插入或更新记录时，该列必须包含一个非空值。
     例如：

     CREATE TABLE table_name (
       id INT NOT NULL,
       name VARCHAR(50) NOT NULL
     );
     

  5. 检查约束（Check Constraint）（MySQL 8.0.16以后才支持）：
     检查约束用于定义自定义条件，以确保插入或更新的数据满足特定的条件。可以使用表达式或函数来定义检查约束。
     例如：

     CREATE TABLE table_name (
       age INT,
       CONSTRAINT age_check CHECK (age >= 18)
     );
     

• 自动增长： AUTO_INCREMENT

• 默认约束：DEFAULT

root@ydh 12:14 mysql>create table grade(id int primary key,grade int default 60); 
Query OK, 0 rows affected (0.10 sec)

root@ydh 14:37 mysql>insert into grade(id,grade) values(1,82);
Query OK, 1 row affected (0.01 sec)

root@ydh 14:37 mysql>insert into grade(id) values(2);
Query OK, 1 row affected (0.01 sec)

root@ydh 14:37 mysql>select * from grade;
+----+-------+
| id | grade |
+----+-------+
|  1 |    82 |
|  2 |    60 |
+----+-------+
2 rows in set (0.01 sec)

Primary Key

在MySQL中，PRIMARY KEY（主键）用于唯一标识表中的每一行数据。主键可以由一个或多个列组成，它们的值必须是唯一的且不为空。主键提供了快速的数据访问和**索引功能，**有助于提高查询性能和确保数据的完整性。

以下是主键的一些详细解释：

1. 唯一性：主键的值在表中必须是唯一的，即每行数据都必须具有不同的主键值。这样可以确保表中没有重复的数据行。

2. 非空性：主键列的值不能为空，每个数据行都必须有一个有效的主键值。这样可以避免在主键列中存在空值，确保数据的完整性。

3. 快速访问：MySQL在主键列上自动创建一个索引，这使得通过主键进行数据查找和访问非常快速和高效。主键索引可以加速数据的检索，尤其是在使用主键作为查询条件时。

4. 唯一标识行：主键可用于唯一标识表中的每一行数据。在关联表之间建立关系时，主键常用作外键的引用。

在创建表时，你可以指定一个或多个列作为主键。有两种常见的方式来定义主键：

• 在列定义中添加 PRIMARY KEY 约束：你可以在列定义中使用 PRIMARY KEY 约束将一个或多个列指定为主键。例如：

CREATE TABLE table_name (
  column1 datatype PRIMARY KEY,
  column2 datatype,
  ...
);

复合主键

create table t4(id int,name varchar(20),primary key(id,name));

root@ydh 15:09 mysql>insert into t4(id,name) values(1,'l');
Query OK, 1 row affected (0.00 sec)

root@ydh 15:10 mysql>insert into t4(id,name) values(1,'s');
Query OK, 1 row affected (0.01 sec)

root@ydh 15:10 mysql>insert into t4(id,name) values(1,'s');
ERROR 1062 (23000): Duplicate entry '1-s' for key 'PRIMARY'

• 在表的结尾处使用 PRIMARY KEY 关键字：你可以在列定义之后使用 PRIMARY KEY 关键字来指定主键。例如：

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
  PRIMARY KEY (column1)
);

无论使用哪种方式，主键的定义都要求主键列的值是唯一的且不为空。

主键对于确保数据的唯一性和完整性非常重要。它可以帮助你建立关系和优化查询，同时提供一种有效的方式来标识表中的每一行数据。

主键、外键

在MySQL中，主键（Primary Key）和外键（Foreign Key）是用于建立表与表之间关系和确保数据完整性的重要概念。

1. 主键（Primary Key）：

   • 主键是用于唯一标识表中每一行数据的列或一组列。主键的值必须是唯一的且不为空。
   • 在MySQL中，常见的主键定义是在表的列上使用 PRIMARY KEY 约束。例如：PRIMARY KEY (column_name)。
   • 主键的作用包括：确保表中的每一行都具有唯一标识，加速数据访问和查询操作，以及作为其他表中外键的引用。

2. 外键（Foreign Key）：

   • 外键用于建立表与表之间的关系。它定义了一个列或一组列，用于引用另一个表中的主键。外键创建了**父表（关联表）和子表（引用表）**之间的连接。
   • 在MySQL中，可以在表的列上使用 FOREIGN KEY 约束来定义外键关系。例如：FOREIGN KEY (column_name) REFERENCES other_table(primary_key_column)。
   • 外键的作用包括：确保引用表中的数据与关联表中的数据保持一致性，维护表与表之间的关系，以及支持关系型数据库的数据完整性。

通过主键和外键的组合，可以建立起表与表之间的关系，并确保数据的一致性和完整性。主键用于唯一标识和快速访问表中的数据行，而外键用于建立表之间的关联关系，并确保关联的数据的一致性。

需要注意的是，要使用外键约束，需要确保相关的表使用的存储引擎支持外键功能，如InnoDB。默认的存储引擎InnoDB对外键提供了支持，但其他存储引擎如MyISAM并不支持外键。

使用主键和外键可以建立关系型数据库中的表之间的关联关系，从而更好地组织和管理数据。

外键的缺点

尽管外键在MySQL中具有许多优点，如确保数据完整性和建立表之间的关系，但也存在一些缺点，如下所述：

1. 性能影响：使用外键可能对性能产生一定影响。外键需要进行额外的数据验证和维护，这可能导致插入、更新和删除操作的性能下降。外键约束还会引入查询开销，因为数据库需要验证关联表中的数据完整性。

2. 数据操作限制：外键约束可能会限制某些数据操作。例如，在删除具有外键关联的记录时，必须确保没有其他表依赖于该记录，否则删除操作将失败。这种限制可能会增加开发和维护的复杂性。

3. 数据库设计复杂性：外键的使用需要进行合理的数据库设计和规划。在建立外键关系之前，需要仔细考虑表之间的关系和数据完整性要求。不正确或不恰当的外键使用可能会导致数据不一致或冗余的情况。

4. 数据库引擎限制：并非所有的MySQL存储引擎都支持外键约束。例如，MyISAM存储引擎不支持外键。如果要使用外键，必须选择支持外键的存储引擎，如InnoDB。

5. 维护困难性：当数据库结构需要更改时，包括添加、修改或删除外键，可能需要小心处理和维护相关的依赖关系。如果不正确地处理外键的修改或删除，可能会导致数据不一致或错误。

6. 产生临时表，默认使用交叉连接，很消耗cpu和内存等资源，尽量避免使用外键。