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

概述

从今天开始, 小白我将带领大家一起来补充一下数据库的知识.

数据完整性

数据完整性 (Data Integrity) 指存储在数据库 (Databse) 中的数据, 需要保证一致性和可靠性. 数据完整性可以防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作.

数据完整性包括:

实体完整性: RDBMS 中一行代码表示一个实体. 实体完整性就是保住每一个实体都能被区别
域完整性: 对列的输入的要求, 通过限制类的数据类型, 格式或值的范围来实现
参照完整性: 主要是表与表之间的关系, 可以通过外键来实现
自定义完整性: 借助存储过程和触发器实现

实体完整性

实体完整性 (Enity Integrity) 要求每张表都有唯一标识符, 每张表中的主键字段不能为空且不能重复.

例如:

id
编号

域完整性

域完整性 (Domain Integrity) 是针对某一具体关系数据库约束条件. 域完整性能保证表中某些列不能输入无效的值.

例如:

数据类型
格式
值域范围
是否允许空值

举个例子, 当我们录入工资数据的时候, 工资的范围应该确保负数不会被录入.

参照完整性

参照完整性 (Referential Integrity) 指关联的两个表之间的约束. 参照完整性保证了表中每条记录外键的值必须是表中存在的.

自定完整性

自定义完整性是针对某一具体关系数据库的约束条件. 自定义完整性反映某一具体应用所涉及的数据必须满足语义要求.

唯一性

在 MySQL 中, 可以使用关键字UNIQUE实现字段的唯一性约束, 从而保证实体的完整性.

UNIQUE 的特性:

使用 UNIQUE, 则表中两条数据的同一个字段不能有相同值
一个表中可以有多个 UNIQUE 约束

PHP 代码实现:

<?php

# 创建连接
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 查看是否连接成功
if ($conn) {
    echo "服务器连接成功!";
} else {
    echo mysqli_connect_error();
}

# SQL语句 (身份证带唯一性)
$SQL = "CREATE TABLE user_with_id (
    id INT(8) AUTO_INCREMENT PRIMARY KEY comment '主键id',
    username VARCHAR(255) NOT NULL comment '姓名',
    id_number varchar(255) UNIQUE NOT NULL comment '身份证号'
)";

# 执行SQL语句
$result = mysqli_query($conn, $SQL);

# 查看是否执行成功
if ($result) {
    echo "SQL语句执行成功!";
} else {
    echo mysqli_error($conn);
}

# 关闭连接
mysqli_close($conn);

?>

效果:

外键

外键 (Foreign KEY) 定义了表之间一致性, 用于强制参照完整性. 外键约束定义了对同一表或其他表的列的引用, 这些列具有 PRIMARY KEY 或 UNIQUE 约束.

创建主表 & 从表

<?php

# 创建连接
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 查看是否连接成功
if ($conn) {
    echo "服务器连接成功!";
} else {
    echo mysqli_connect_error();
}

# SQL语句, 创建学生表
$SQL1 = "CREATE TABLE student (
    student_id INT(8) PRIMARY KEY COMMENT '学生id',
    student_name VARCHAR(255) NOT NULL COMMENT '学生姓名'
)";

# SQL语句, 创建成绩表
$SQL2 = "CREATE TABLE grade (
    id INT(8) AUTO_INCREMENT PRIMARY KEY COMMENT '主键id',
    student_id INT(8) NOT NULL COMMENT '学生id',
    course varchar(255) NOT NULL COMMENT '课程',
    grade INT(8) NOT NULL COMMENT '成绩',
    FOREIGN KEY (student_id) references student(student_id)
)";

# 执行SQL语句
$result = mysqli_query($conn, $SQL1);

# 查看是否执行成功
if ($result) {
    echo "SQL语句执行成功!";
} else {
    echo mysqli_error($conn);
}

# 执行SQL语句
$result = mysqli_query($conn, $SQL2);

# 查看是否执行成功
if ($result) {
    echo "SQL语句执行成功!";
} else {
    echo mysqli_error($conn);
}

# 关闭连接
mysqli_close($conn);

?>

效果:

插入数据

<?php

# 创建连接
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 查看是否连接成功
if ($conn) {
    echo "服务器连接成功!";
} else {
    echo mysqli_connect_error();
}

# SQL语句, 向主表添加数据
$SQL1 = "INSERT INTO student
    (student_id, student_name)
    VALUES(1, '我是小白呀'),
    (2, '我是大白呀')";

# 执行SQL语句
$result = mysqli_query($conn, $SQL1);

# SQL语句, 向从表添加数据
$SQL2 = "INSERT INTO grade
    (student_id, course, grade)
    VALUES(1, '语文', 59),
    (2, '语文', 99)";

# 执行SQL语句
$result = mysqli_query($conn, $SQL2);

# 查看是否执行成功
if ($result) {
    echo "SQL语句执行成功!";
} else {
    echo mysqli_error($conn);
}

# 关闭连接
mysqli_close($conn);

?>

错误展示

<?php

# 创建连接
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 查看是否连接成功
if ($conn) {
    echo "服务器连接成功!";
} else {
    echo mysqli_connect_error();
}

# SQL语句, 错误展示 (主表没有student_id=3)
$SQL = "INSERT INTO grade
    (student_id, course, grade)
    VALUES(3, '语文', 88)";

# 执行SQL语句
$result = mysqli_query($conn, $SQL);

# 查看是否执行成功
if ($result) {
    echo "SQL语句执行成功!";
} else {
    echo mysqli_error($conn);
}

# 关闭连接
mysqli_close($conn);

?>

报错:

服务器连接成功!
Fatal error: Uncaught mysqli_sql_exception: Cannot add or update a child row: a foreign key constraint fails (`study`.`grade`, CONSTRAINT `grade_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)) in C:\Users\Windows\Desktop\数据库\外键错误.php:19
Stack trace:
#0 C:\Users\Windows\Desktop\数据库\外键错误.php(19): mysqli_query(Object(mysqli), 'INSERT INTO gra...')
#1 {main}
thrown in C:\Users\Windows\Desktop\数据库\外键错误.php on line 19
PHP Fatal error: Uncaught mysqli_sql_exception: Cannot add or update a child row: a foreign key constraint fails (`study`.`grade`, CONSTRAINT `grade_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)) in C:\Users\Windows\Desktop\数据库\外键错误.php:19
Stack trace:
#0 C:\Users\Windows\Desktop\数据库\外键错误.php(19): mysqli_query(Object(mysqli), 'INSERT INTO gra...')
#1 {main}
thrown in C:\Users\Windows\Desktop\数据库\外键错误.php on line 19

频道导航