mysql alter trigger_MySQL 学习笔记（三）：完整性和触发器设计

(一)完整性设计

方法一、在设计表时定义约束

删除数据库school,建立新数据库school1

drop databaseschool;create databaseschool;use school;

1.定义约束

create tableStudent(

Snochar(9) primary key, /*主键约束 提示primary key*/Snamechar(20) unique, /*唯一约束 提示 unique*/Ssexchar(2) check (Ssex='男' or Ssex='女'), /*检查约束 提示 性别只能是男或女 check*/Sageint check (sage <= 100 and sage>=1), /*检查约束 提示年龄介于1 到 100之间 check*/Sdeptchar(20) default 'CS' /*缺省 缺省值为CS, default()*/);create tableCourse(

Cnochar(4) primary key, /*主键约束*/Cnamechar(40) unique, /*唯一约束*/Cpnochar(4) not null, /*非空约束 提示not null*/Ccreditint default 1 /*缺省值为1*/);create tableSC(

Snochar(9),

Cnochar(4),

Gradeint check (grade>=0 and grade<=100), /*约束取值为0 到100 * check /

primary key(Sno, Cno),

foreign key (Sno) references Student(sno) on delete cascade on update cascade,

foreign key (Cno) references Course(Cno) on delete cascade

/*定义(Sno,Cno)为主键*/

/*定义sno为外键参考student表的主键sno,并且实现级联删除更新SC表中相应的元组*/

/*提示 foreign key .. references ... on delete cascade on update cascade,

/*定义Cno为外键参考course表的主键Cno,并且实现级联删除SC表中相应的元组*/);

2. 检验约束

插入下列记录

insert into student values ('95001','李勇','男',20,'CS');insert into student values ('95002', '刘晨','女',19,'IS');insert into student values('95003', '王敏', '女',18, 'MA');insert into student values('95004', '张立', '男',19, 'IS');insert into Course values(1,'数据库', 5,4);insert into Course values(2, '数学', 3 ,2);insert into Course values(3, '信息系统', 1, 4);insert into Course values('4', '操作系统', '6', 3);insert into Course values('5', '数据结构', '7', 4);insert into Course values('6', '数据处理', '', 2);insert into Course values('7', 'PASCAL语言', '6', 4);insert into SC values('95001', '1', 92);insert into SC values('95001', '2', 85);insert into SC values('95001', '3', 88);insert into SC values('95002', '2', 90);insert into SC values('95002', '3', 80);

请设计系列测试用例逐一检查每一个定义的约束是否生效

例如：检查student表的主键约束是否生效,测试用例

insert into student values ('95001','测试','男',20,'CS');

报错：ERROR 1062 (23000): Duplicate entry '95001' for key 'PRIMARY'

检查student表的唯一约束是否生效,测试用例

insert into student values ('95005','李勇','男',20,'CS');

报错：ERROR 1062 (23000): Duplicate entry '李勇' for key 'Sname'

检查student表的检查约束是否生效,测试用例

insert into student values ('95005','李刚','牛',20,'CS');

插入成功 Query OK, 1 row affected (0.00 sec)。其实这里理论上来说应该是插入失败的，因为 student 有检查约束，但不同于SQL，在MYSQL中，CHECK只是一段可调用但无意义的子句。MySQL会直接忽略。

方法二、在表定义完成后，添加修改约束

建立新数据库school2

createdatabaes school2;use school2;

1. 定义表

create tablestudent(

Snochar(9),

Snamechar(20),

Ssexchar(2) ,

Sageint,

Sdeptchar(20)

);create tableCourse(

Cnochar(4),

Cnamechar(40),

Cpnochar(4) ,

Ccreditint);create tableSC(

Snochar(9),

Cnochar(4),

Gradeint);

2.添加修改约束

(1). 添加主键约束

alter table 表名 add constraint 约束名(形如：PK_表名) primary key (主键)

alter table Student add constraint PK_Student primary key(Sno);alter table Course add constraint PK_Course primary key(Cno);alter table Sc add constraint PK_SC primary key(Sno, Cno);

(2). 添加外键约束

alter table 从表 add constraint 约束名(形如：FK_从表_主表) foreign key(属性名) references 主表(属性名)

alter table SC add constraint SC_Student foreign key(sno) referencesStudent(sno);alter table SC add constraint SC_Course foreign key(cno) references Course(cno);

(3). 外键约束 + 级联(删除/更新)

alter table 从表 add constraint 约束名 foreign key(属性名) references 主表(属性名)ON DELETE CASCADE ON UPDATE CASCADE

alter table SC add constraint SC_Students foreign key(sno) references Student(sno) on delete cascade on update cascade;alter table SC add constraint SC_Courses foreign key(cno) references Course(cno) on delete cascade;

发现报错Can't write; duplicate key in table '#sql-1f2_7'，发现原来是这里的约束名和前面的重复了，需要重新命名一下。

(4). 添加唯一约束

alter table 表名 add constraint 约束名(形如：UQ_表名) unique (属性名)

alter table Student add constraint UQ_Student unique(sname);alter table Course add constraint UQ_Course unique(cname);

(5). 添加默认约束

alter table表名alter column字段名drop default; (若本身存在默认值，则先删除)

alter table表名 alter column字段名 set default默认值;(若本身不存在则可以直接设定)

alter table student alter column sdept set default 'CS';alter table course alter column ccredit set default 1;

(6). 添加检查check约束

alter table 表名 add constraint 约束名(形如：CK_表名) check (属性名 约束条件 )

alter table Student add constraint CK_Student check(Ssex='男' or Ssex='女');alter table Student add constraint CK_Student check(sage <= 100 and sage>=1);Alter table Course add constraint CK_Course check(grade>=0 and grade<=100);

2.检查约束是否生效

参考方法一设计测试用例，逐一检查！

结果：测试后发现跟方法一出现的结果是一样的。

3. 删除约束

删除主键约束：alter table 表名 drop primary key;

删除外键约束：alter table 表名 drop foreign key 外键(区分大小写);

设计测试用例，检查删除约束后是否生效？

添加多个约束，情况如何？

这里我删除约束的时候报错了：ERROR 1025 (HY000): Error on rename of './school/#sql-1f2_7' to './school/student' (errno: 150 - Foreign key constraint is incorrectly formed)

查了下资料，发现 MySQL建立外键的字段必须和引用表的字段一模一样的类型。 这里我创建外键的时候 sno 中有些表有主键约束，有些却没有，所以当我删除主键约束的时候它会提示外键被错误建立起来了。但我不明白为什么建立外键的时候没有报错。

(二)触发器

MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

创建触发器

在MySQL中，创建触发器语法如下：

CREATE TRIGGERtrigger_name

trigger_time

trigger_eventONtbl_nameFOREACH ROW

trigger_stmt

其中：

trigger_name：标识触发器名称，用户自行指定；

trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；

trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；

tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；

trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

删除触发器

drop trigger 触发器名

写个触发器：

delimiter $$create trigger hiChange after insert on student foreach rowbegin

select 'hi，成功插入数据' into @ee; /*使用 select 语句来打印输出*/

end$$

delimiter ;

刚开始创建的时候报错：Not allowed to return a result set from a trigger。原因：在mysql的trigger和function中不能出现select * from table形式的查询，因为其会返回一个结果集；而这在mysql的trigger和function中是不可接受的，但是在存储过程中可以。在 select 语句后加上 into @ee 就可以了。