1.1 数据完整性
数据完整性是数据库设计的核心原则之一, 它确保了数据的精确性和可靠性, 从而维护了数据库的质量.
为了防止数据库中存储不符合语义规定的数据, 以及防止由于错误信息的输入或输出导致的无效操作或错误信息的产生, 引入了约束这一概念.
SQL规范通过约束对表数据进行额外的条件限制, 以确保数据的完整性和一致性.
这些约束在多个层面上起到了关键作用, 具体如下:
* 1. 实体完整性(Entity Integrity):
实体完整性保证了表中的每一行数据都是唯一的, 可以被准确地识别和区分.
例如, 在一个包含员工信息的表中, 每个员工都应该有一个唯一的标识, 如员工ID, 以确保不会有两条完全相同的记录存在.
这有助于防止数据冗余和混淆.
* 2. 域完整性(Domain Integrity):
域完整性关注的是数据列中值的范围或格式. 它确保了数据列中的值符合预定义的标准或条件.
例如, 年龄列的值应该限制在0到120之间, 性别列的值只能是'男'或'女'.
这些限制有助于防止无效或不合理的数据进入数据库.
* 3. 引用完整性(Referential Integrity):
引用完整性确保了两个表之间的关系得到正确的维护.
当一个表中的记录引用另一个表中的记录时, 引用完整性要求被引用的记录必须存在.
例如, 员工表中的部门字段必须对应于部门表中实际存在的部门记录.
这有助于保持数据的一致性, 并防止由于引用不存在的记录而导致的错误.
* 4. 用户自定义完整性(User-defined Integrity):
除了上述标准约束外, 用户还可以根据特定的业务需求定义自定义的完整性规则.
这些规则可以根据特定的业务逻辑或条件对数据进行限制.
例如, 可以规定用户名必须是唯一的, 密码字段不能为空, 或者部门经理的工资不得超过本部门员工平均工资的某个倍数.
这些自定义完整性规则有助于确保数据库中的数据符合特定的业务要求.
综上所述, 约束在数据库设计中扮演着至关重要的角色.
它们确保了数据的完整性和一致性, 从而提高了数据库的质量和可靠性.
通过合理地应用这些约束, 我们可以有效地防止数据错误和无效操作的发生, 保护数据库免受潜在的风险和损害.
1.2 约束类型
约束是数据库表级的重要强制规定, 它们确保了表中数据的完整性和准确性.
常见的约束类型包括:
* 1. NOT NULL 非空约束: 确保字段不能为空, 即必须包含值.
* 2. UNIQUE 唯一约束: 确保字段或字段组合在表中的值是唯一的, 但不排除NULL值(除非列被定义为NOT NULL).
* 3. PRIMARY KEY 主键约束: 标识表中的唯一记录, 一个表只能有一个主键, 且主键字段的值不能为空.
* 4. FOREIGN KEY 外键约束: 定义了两个表之间的关联关系, 确保子表中的记录所引用的父表记录确实存在.
* 5. DEFAULT 默认值约束: 为列提供默认值, 当插入新记录但没有为该列提供值时, 将使用此默认值
这些约束类型可以根据具体的应用场景和需求进行选择和组合, 以确保数据库中的数据完整性和准确性.
根据不同的标准和需求, 可以分为多种类型:
* 1. 根据约束数据列的限制, 约束可分为:
1. 单列约束: 这种约束作用于表中的单个列, 确保该列的数据满足特定的条件或规则.
例如, NOT NULL约束就是一个典型的单列约束, 它要求该列不能有NULL值.
2. 多列约束: 与单列约束不同, 多列约束涉及表中的多个列, 确保这些列的组合满足特定的条件或规则.
例如, UNIQUE约束可以应用于多个列的组合, 以确保这些列的组合值在整个表中是唯一的.
* 2. 根据约束的作用范围, 约束可分为:
1. 列级约束: 列级约束仅对表中的单个列起作用, 它们直接定义在列的数据类型之后.
这种约束是针对特定列的, 并且只影响该列的数据.
列级约束既可以在创建表时定义, 也可以在表创建后通过ALTER TABLE语句添加.
2. 表级约束: 与列级约束不同, 表级约束可以作用于表中的多个列, 它们不与特定的列一起定义, 而是作为表的独立部分进行定义.
表级约束通常用于定义跨多个列的关系或条件, 例如外键约束.
在创建表时, 可以直接通过CREATE TABLE语句来定义这些约束, 以在数据插入或更新时实施特定的规则和条件.
同样地, 如果表已经存在, 也可以使用ALTER TABLE语句来添加, 修改或删除约束.
1.3 查看约束
information_schema是一个特殊的数据库, 它包含了关于其他所有数据库和表的元数据信息.
table_constraints表是information_schema中用于存储关于各个表的约束信息.
下面是一个SQL查询的示例, 该查询会返回指定表(比如your_table_name)的所有约束:
SELECT *
FROM information_schema.table_constraints
WHERE table_schema = 'your_database_name'
AND table_name = 'your_table_name';
请注意, 需要替换your_database_name为你的实际数据库名称, 以及your_table_name为你想要查询的表名.
mysql> SELECT *
FROM information_schema.table_constraints
WHERE table_schema = 'atguigudb'
AND table_name = 'employees';
+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+
| def | atguigudb | emp_email_uk | atguigudb | employees | UNIQUE | YES |
| def | atguigudb | emp_emp_id_pk | atguigudb | employees | UNIQUE | YES |
| def | atguigudb | PRIMARY | atguigudb | employees | PRIMARY KEY | YES |
| def | atguigudb | emp_dept_fk | atguigudb | employees | FOREIGN KEY | YES |
| def | atguigudb | emp_job_fk | atguigudb | employees | FOREIGN KEY | YES |
| def | atguigudb | emp_manager_fk | atguigudb | employees | FOREIGN KEY | YES |
+
6 rows in set (0.00 sec)
列名解释:
CONSTRAINT_CATALOG: 约束目录名(在MySQL中通常为def).
CONSTRAINT_SCHEMA: 约束所在的数据库名.
CONSTRAINT_NAME: 约束的名称.
TABLE_SCHEMA: 表所在的数据库名.
TABLE_NAME: 约束所属的表名.
CONSTRAINT_TYPE: 约束的类型(如PRIMARY KEY, FOREIGN KEY, UNIQUE等).
ENFORCED: 约束是否被强制执行 (通常为YES).
行数据解释:
emp_email_uk: 唯一, 确保employees表中的email列的值唯一.
emp_emp_id_pk: 主键, 确保employees表中的emp_id列的值是唯一的, 并且不为NULL.
emp_dept_fk: 外键, 确保employees表中的department_id列的值在departments表的dept_id列中存在.
emp_job_fk: 外键, 确保employees表中的job_id列的值在jobs表的job_id列中存在.
emp_manager_fk: 外键, 确保employees表中的manager_id列的值在employees表的employee_id列中存在.
PRIMARY: 每个主键约束都应该有一个唯一的名称, 而不是简单地使用PRIMARY.
但在某些数据库系统或特定的数据库实现中, 如果主键约束没有被显式地命名, 系统可能会自动为其分配一个默认名称, 如PRIMARY.
