MySQL 类似断言的约束

发布于 2024-12-26 15:33:01 字数 535 浏览 1 评论 0原文

我是MySQL新手，我刚刚发现它不支持断言。

我得到了这张桌子：

   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 次。

我怎样才能在没有断言的情况下管理它？

谢谢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

碍人泪离人颜 2025-01-02 15:33:01

这绝对是表“guest”上的 BEFORE INSERT 触发器的味道：

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，这将拒绝插入。

在 MySQL 5.1.41-3ubuntu12.10 (Ubuntu Lucid) 上测试并适用于我

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)

回复收藏 0 原文

~没有更多了~