Question

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

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.

我如何在没有断言的情况下加以管理?

感谢。

Answer 1

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套卡——编号为NUL。由于卡号数被宣布为NOT NUL,这将拒绝插入。

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

友情链接