mysql检查约束无效_mysql – 检查约束不起作用？

你需要的是两个触发无效年龄条件的触发器

>插入之前

>在更新之前

以下是基于“触发器验证数据”子标题下的第MySQL Stored Procedure Programming章第254-256页的MySQL触发器的简单错误捕获方法：

drop table mytable;

create table mytable (

id smallint unsigned AUTO_INCREMENT,

age tinyint not null,

primary key(id)

);

DELIMITER $$

CREATE TRIGGER checkage_bi BEFORE INSERT ON mytable FOR EACH ROW

BEGIN

DECLARE dummy,baddata INT;

SET baddata = 0;

IF NEW.age > 20 THEN

SET baddata = 1;

END IF;

IF NEW.age < 1 THEN

SET baddata = 1;

END IF;

IF baddata = 1 THEN

SELECT CONCAT('Cannot Insert This Because Age ',NEW.age,' is Invalid')

INTO dummy FROM information_schema.tables;

END IF;

END; $$

CREATE TRIGGER checkage_bu BEFORE UPDATE ON mytable FOR EACH ROW

BEGIN

DECLARE dummy,baddata INT;

SET baddata = 0;

IF NEW.age > 20 THEN

SET baddata = 1;

END IF;

IF NEW.age < 1 THEN

SET baddata = 1;

END IF;

IF baddata = 1 THEN

SELECT CONCAT('Cannot Update This Because Age ',NEW.age,' is Invalid')

INTO dummy FROM information_schema.tables;

END IF;

END; $$

DELIMITER ;

insert into mytable (age) values (10);

insert into mytable (age) values (15);

insert into mytable (age) values (20);

insert into mytable (age) values (25);

insert into mytable (age) values (35);

select * from mytable;

insert into mytable (age) values (5);

select * from mytable;

结果如下：

mysql> drop table mytable;

Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable (

-> id smallint unsigned AUTO_INCREMENT,

-> age tinyint not null,

-> primary key(id)

-> );

Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER $$

mysql> CREATE TRIGGER checkage_bi BEFORE INSERT ON mytable FOR EACH ROW

-> BEGIN

-> DECLARE dummy,baddata INT;

-> SET baddata = 0;

-> IF NEW.age > 20 THEN

-> SET baddata = 1;

-> END IF;

-> IF NEW.age < 1 THEN

-> SET baddata = 1;

-> END IF;

-> IF baddata = 1 THEN

-> SELECT CONCAT('Cannot Insert This Because Age ',NEW.age,' is Invalid')

-> INTO dummy FROM information_schema.tables;

-> END IF;

-> END; $$

Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TRIGGER checkage_bu BEFORE UPDATE ON mytable FOR EACH ROW

-> BEGIN

-> DECLARE dummy,baddata INT;

-> SET baddata = 0;

-> IF NEW.age > 20 THEN

-> SET baddata = 1;

-> END IF;

-> IF NEW.age < 1 THEN

-> SET baddata = 1;

-> END IF;

-> IF baddata = 1 THEN

-> SELECT CONCAT('Cannot Update This Because Age ',NEW.age,' is Invalid')

-> INTO dummy FROM information_schema.tables;

-> END IF;

-> END; $$

Query OK, 0 rows affected (0.07 sec)

mysql> DELIMITER ;

mysql> insert into mytable (age) values (10);

Query OK, 1 row affected (0.06 sec)

mysql> insert into mytable (age) values (15);

Query OK, 1 row affected (0.05 sec)

mysql> insert into mytable (age) values (20);

Query OK, 1 row affected (0.04 sec)

mysql> insert into mytable (age) values (25);

ERROR 1172 (42000): Result consisted of more than one row

mysql> insert into mytable (age) values (35);

ERROR 1172 (42000): Result consisted of more than one row

mysql> select * from mytable;

+----+-----+

| id | age |

+----+-----+

| 1 | 10 |

| 2 | 15 |

| 3 | 20 |

+----+-----+

3 rows in set (0.00 sec)

mysql> insert into mytable (age) values (5);

Query OK, 1 row affected (0.07 sec)

mysql> select * from mytable;

+----+-----+

| id | age |

+----+-----+

| 1 | 10 |

| 2 | 15 |

| 3 | 20 |

| 4 | 5 |

+----+-----+

4 rows in set (0.00 sec)

mysql>

另请注意,自动增量值不会浪费或丢失.

试试看 ！！！