【数据库系统概论】数据模型之 MySQL完整性约束

数据完整性约

数据完整性约束是在表和字段上强制执行的数据检验规则，为了防止不规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，主要是对空值和重复值的约束，使不符合规范的数据不能进入数据库，以保证数据存储的完整性和准确性。
完整性分类

按照约束的不同类型可以分为

实体完整性（primary key、unique、auto_increment）、

域完整性（not null、default）、

参照完整性（foreign key）、

用户自定义完整性（check）。

 

实体完整性

用来标识表中的每一条记录，一条记录代表一个实体(entity)。
主键约束(primary key)

1.     每个表中只能有一个主键。
2.     主键值须非空不重复。
3.     可设置单字段主键，也可设置多字段联合主键。
4.     联合主键中多个字段的数据完全相同时，才违反主键约束。

列级添加主键约束：

mysql> create table employee(
      e_id int primary key,
    e_name varchar(5),
      e_sex varchar(5),
     e_age int,
      d_id int);

表级添加主键约束：

 create table student1
              (
                sno char(6) primary key,
                sname varchar(8),
                ssex char(2),
                sbirth date,
                sdept varchar(20),
                constraint fk_s1_01 foreign key(sdept)references department(sdept)
--                 这个就是department是参照表， 如果department中如果数据删除后,那么student1中置空  fk_s1_01 是约束的名称
                on delete set null
--                 如果department中如果数据改变后,那么student1中也改变
                on update cascade

                );

 

唯一约束(unique)

• 指定列的数据不能重复。
• 可以为空，但只能出现一个空值。

 

自动增长列(auto_increment)

• 指定字段的数据自动增长。
• 配合主键一起使用，并且只适用于整数类型。
• 默认从1开始，每增加一条记录，该字段的值会增加1。
• 即使数据删除，还是从删除的序号继续增长。

域完整性

域完整性是针对单元格的约束，不与其它行参考比较。

非空约束(not null)

• 字段的值不能为空。

默认约束(default)

• 如果新插入一条记录时没有为该字段赋值，系统会自动为这个字段赋值为默认约束设定的值。
• 如果插入的数据为“null”，则不会使用默认值，只有没有插入数据时候，才会使用默认值。

参照完整性

参照完整性指多表之间的对应关系，在一张表中执行数据插入、更新、删除等操作时，DBMS都会跟另一张表进行对照，避免不规范的操作，以确保数据存储的完整性。
外键约束(foreign key)

    某一表中某字段的值依赖于另一张表中某字段的值
    主键所在的表为主表，外键所在的表为从表
    每一个外键值必须与另一个表中的主键值相对应

为什么不推荐使用外键约束

用户自定义完整性

根据用户实际的完整性要求来定义，在执行数据插入、更新等操作时，DBMS会检查数据是否满足检查约束中的限定条件，避免不符合条件的操作，以保证数据存储的准确性。

检查约束(check)

• 指定需要检查的限定条件。

完整性约束实操案例

注意事项：数据库管理工具phpmyadmin的默认引擎为myisam，该引擎性不能进行事务处理，可以将数据库引擎切换为InnoDB或者在建表时指定表类型。
修改引擎的方法（phpmyadmin）:

1、打开mysql的my.ini文件，找到 [mysqld]块，在下面加入default-storage-engine=INNODB
2、重启数据库。

 

数据库设计如下：

 

 

-- 参照完整性
-- 
--      学生表的系部参照系部表的系部

create table department 

 (

    sdept varchar(20) primary key,

    pic varchar(8),

    address varchar(20)

);

      insert into department(sdept,pic,address)values("计算机科学与技术","郑小东","05B423");

      insert into department(sdept,pic,address)values("软件工程","王红梅","05B501");

      insert into department(sdept,pic,address)values("网络工程","赵学民","05B421");

      insert into department(sdept,pic,address)values("互联网工程","金秋春","05A509");

      select * from department;


       create table student1

              (

                sno char(6) primary key,

                sname varchar(8),

                ssex char(2),

                sbirth date,

                sdept varchar(20),

                constraint fk_s1_01 foreign key(sdept)references department(sdept)

--                 这个就是department是参照表， 如果department中如果数据删除后,那么student1中置空 
                on delete set null
--                 如果department中如果数据改变后,那么student1中也改变
                on update cascade

                );

                

                insert into student1(sno,sname,ssex,sbirth,sdept)

                values("091001","郑航飞","男","1990-09-18","软件工程");

                insert into student1(sno,sname,ssex,sbirth,sdept)

                values("091002","李志勇","男","1990-09-18","网络工程");

                insert into student1(sno,sname,ssex,sbirth,sdept)

                values("091003","肖娜","女","1990-09-18","软件工程");

                insert into student1(sno,sname,ssex,sbirth,sdept)

                values("091004","谢菲菲","女","1990-09-18","互联网工程");

                

                delete from department where sdept="互联网工程";

                update department set sdept="网络安全" where sdept="网络工程";

                select * from  department;

                select * from student1;

                

--     参照关系和被参照关系可以是同一个关系

create table course
(

            cno char(3) primary key,

            cname varchar(30),

            ccredit tinyint,

            cpno char(3),

            constraint fk_c_01 foreign key(cpno)references course(cno)

);

        

insert into course(cno,cname,ccredit,cpno)values("001","计算机基础",2,null);

insert into course(cno,cname,ccredit,cpno)values("002","c语言",4,"001");
  # 如果只执行第二条插入语句， 会报错因为你参照的数据不存在
    # Cannot add or update a child row: a foreign key constraint fails (`homework`.`course`, CONSTRAINT `fk_c_01` FOREIGN KEY (`cpno`) REFERENCES `course` (`cno`))


select * from course;

        

        
-- 
-- 一个表参考多个表, 这个类型和大小都应该和参照表一致
-- sc表是选课表
create table sc
(
  sno char(6),
  cno char(3),
  grade tinyint
-- 下面两行的效果和alter table sc add constraint fk_sc_01 foreign key (sno) references student(sno);一样，只不过会在插入数据中有影响
--     add constraint fk_sc_01 foreign key (sno) references student(sno);
--     constraint fk_sc_02 foreign key (cno) references course(cno);

);

  

select * from student1;

select * from course;

select * from sc;

insert into sc(sno,cno,grade)values("091001","001",87);

insert into sc(sno,cno,grade)values("091001","002",88);

alter table sc add constraint fk_sc_01 foreign key (sno) references student(sno);

alter table sc add constraint fk_sc_02 foreign key (cno) references course(cno);
select * from sc;


删除参照完整性

alter table student1 drop foreign key fk_s1_01;

修改表数据和删除表数据：

-- course表中
delete from course where cno = "001";
#这个是删除某一条数据 如果多个条有都有这个数据那么删除所有条

update course set cname = '计算机基础' where cno = "001"
#如何修改course表， 中刚才那个C语言中的先修课应该是计算机基础 cno是001；

 参考链接  https://blog.csdn.net/m0_52528565/article/details/124707698

                https://blog.csdn.net/kejiayuan0806/article/details/100766658