MySQL与PHP的基础与应用专题之数据完整性

MySQL与PHP的基础与应用专题之数据完整性攻略

1. 数据完整性的概念

数据完整性是指数据的准确性、合法性、一致性和有效性等方面。

在数据库中，可以通过设置约束条件来保证数据的完整性，包括以下几种类型的约束：

• 主键约束
• 外键约束
• 唯一约束
• 非空约束
• 默认约束
• 检查约束

2. 主键约束

主键是唯一标识一张表中某一行数据的字段或字段组合，主键的值必须唯一且不能为空。

创建主键的方式有两种：

1. 在创建表的时候指定主键

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 在已存在的表中添加主键

ALTER TABLE `user` ADD PRIMARY KEY (`id`);

3. 外键约束

外键是关系型数据库中表与表之间关联的一种约束，用于保证数据的一致性。

在使用外键之前，必须先确保所关联的表已经存在主键。

创建外键的方式如下：

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `product` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

上述代码中，外键约束的名称为fk_user_id，关联的列是user_id，所关联的主键是user表中的id字段。

4. 唯一约束

唯一约束用于保证某个字段的值在表中必须唯一，可用于防止数据重复插入等情况。

创建唯一约束的方式如下：

1. 在创建表的时候指定唯一约束

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 在已存在的表中添加唯一约束

ALTER TABLE `user` ADD UNIQUE (`username`);

5. 非空约束

非空约束用于保证某个字段在插入数据时不能为空。

创建非空约束的方式如下：

1. 在创建表的时候指定非空约束

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 在已存在的表中添加非空约束

ALTER TABLE `user` MODIFY `email` varchar(255) NOT NULL;

6. 默认约束

默认约束用于在插入数据时，如果该字段没有被赋值，则会自动赋予一个默认值。

创建默认约束的方式如下：

1. 在创建表的时候指定默认约束

CREATE TABLE `book` (
  `isbn` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` float DEFAULT 0,
  PRIMARY KEY (`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 在已存在的表中添加默认约束

ALTER TABLE `book` MODIFY `price` float DEFAULT 0;

7. 检查约束

检查约束用于保证某个字段的取值在插入数据时符合一定的条件。

创建检查约束的方式如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL CHECK (LENGTH(`username`) > 2),
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

上述代码中，检查约束用于保证username字段的长度大于2。

8. 示例说明

示例1：使用主键约束

假设要创建一个用户表，包含以下字段：

• id：用户id，主键，自增长
• username：用户名，唯一约束
• password：用户密码

创建表的SQL语句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

示例2：使用外键约束

假设要创建一个订单表，包含以下字段：

• id：订单id，主键，自增长
• user_id：用户id，外键约束，关联用户表的id字段
• product：产品名称

创建表的SQL语句如下：

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `product` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在执行上述SQL语句后，当尝试向订单表中插入一个不存在于用户表中的用户id时，会导致插入失败，并且会抛出外键约束错误。这保证了订单表中的用户id一定是存在于用户表中的。