类似于MySQL断言的约束 [英] MySQL assertion-like constraint
问题描述
我是MySQL的新手,我刚刚发现它不支持断言.
I'm a MySQL newbie, I just discovered that it doesn't support assertions.
我有这张桌子:
CREATE TABLE `guest` (
`ssn` varchar(16) NOT NULL,
`name` varchar(200) NOT NULL,
`surname` varchar(200) NOT NULL,
`card_number` int(11) NOT NULL,
PRIMARY KEY (`ssn`),
KEY `card_number` (`card_number`),
CONSTRAINT `guest_ibfk_1` FOREIGN KEY (`card_number`) REFERENCES `member` (`card_number`)
)
我需要的是一个成员最多可以邀请2位客人.
因此,在表guest
中,我需要一个特定的card_number
最多可以出现2次.
What I need is that a member can invite maximum 2 guests.
So, in table guest
I need that a specific card_number
can appear maximum 2 times.
如何在没有断言的情况下进行管理?
How can I manage it without assertions?
谢谢.
推荐答案
这肯定在表'guest'上有BEFORE INSERT触发器的味道:
This definitly smells of a BEFORE INSERT trigger on the table 'guest':
DELIMITER $$
DROP TRIGGER IF EXISTS check_guest_count $$
CREATE TRIGGER check_guest_count BEFORE INSERT ON `guest`
FOR EACH ROW BEGIN
DECLARE numguests int DEFAULT 0;
SELECT COUNT(*) INTO numguests FROM `guest` WHERE card_number=NEW.card_number;
if numguests>=2 THEN
SET NEW.card_number = NULL;
END IF;
END;
$$
DELIMITER ;
这基本上会查询当前的访客数,如果已经> = 2,则将card_number设置为NULL.由于card_number被声明为NOT NULL,因此将拒绝插入.
This basically looks up the current guest count, and if it is already >=2 sets card_number to NULL. Since card_number is declared NOT NULL, this will reject the insert.
在MySQL 5.1.41-3ubuntu12.10(Ubuntu Lucid)上测试并为我工作
Tested and works for me on MySQL 5.1.41-3ubuntu12.10 (Ubuntu Lucid)
这篇关于类似于MySQL断言的约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!