数据库完整性——MySQL添加约束

一． 实验环境：

1、 操作系统：window10
2、 MySQL 5.7

二． 实验内容与完成情况：

1.创建一个教工表teacher（tno，tname，tadd，telphone，tsex，id），将教工号tno设为主键，性别默认值为“男”:

create table teacher(
	tno char(7) primary key,
    tname char(10),
    tadd char(10),
    telphone char(10),
    tsex char(2) default '男',
    id int
)engine=InnoDB

 2.根据教工表teacher完成以下任务。

 
 
设置telphone默认值为00000000:
alter table teacher modify telphone char(10) default '00000000';

 2) 设置tsex的check检查约束为：输入值只能为“男”或“女”:
ALTER TABLE teacher modify tsex enum('男','女') default '男' ;

 3) 设置id的位数为15位或18位，每位都是数字:
 
 先创建一个判断数字的函数IsNum：
delimiter $$
drop function if exists IsNum $$
create function IsNum(str varchar(25))
	returns int
begin
	declare iResult int default 0;
    if isnull(str) then return 0;end if;
    if str='' then return 0;end if;
    select str REGEXP '^[0-9]*$' into iResult;
    if iResult=1 then
		return 1;
	else
		return 0;
	end if;
end $$

alter table teacher modify id char(20);

delimiter //
create trigger TR_ID_IN after insert on teacher
for each row
begin
if (length(new.id)!=15 and length(new.id)!=18) or (IsNum(new.id)!=1) then
signal sqlstate '45000'
set message_text='Insert Error';
end if;
end;//
delimiter ;

delimiter //
create trigger TR_ID_UP after update on teacher
for each row
begin
if (length(new.id)!=15 and length(new.id)!=18) or (IsNum(new.id)!=1) then
signal sqlstate '45000'
set message_text='Update Error';
end if;
end;//
delimiter ;