mysql断言命令,类似于MySQL断言的约束

I'm a MySQL newbie, I just discovered that it doesn't support assertions.

I got this table:

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`)

)

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?

Thanks.

解决方案

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 ;

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.

Tested and works for me on MySQL 5.1.41-3ubuntu12.10 (Ubuntu Lucid)