MySQL与PHP的基础与应用专题之数据完整性(2)-木庄网络博客

插入数据

<?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

到此这篇关于MySQL与PHP的基础与应用专题之数据完整性的文章就介绍到这了,更多相关MySQL 数据完整性内容请搜索

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容