MySQL assertion-like constraint

Question

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.

Not last time I looked: confirmed in [7.3 docs](http://www.postgresql.org/docs/7.3/static/sql-createtable.html) as not implemented but not mentioned either way in [9.1 docs](http://www.postgresql.org/docs/9.1/static/sql-createtable.html)? — onedaywhen, Jan 13 '12 at 13:49

score 3 · Accepted Answer · answered Jan 12 '12 at 20:11

3

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)

answered Jan 12 '12 at 20:11

Eugen Rieck

64,175
10
70
92

Yes... but wouldn't raising an exception to say you've already got 2 in there be better rather than failing silently to do what the user asked? – Ben Jan 12 '12 at 20:15
We do not fail silently! The insert query itself failes (with "card_number cannot be NULL"), which can be caught at the code level, from which it is executed against the DB. – Eugen Rieck Jan 12 '12 at 20:23
Ok, I see it gives a feedback. What about who complains about not using trigger in order to emulate constraints, because of the non-acid property of mysql? – Jan 12 '12 at 20:25
Yes. You send a query like `INSERT INTO guest VALUES (...);` from your programming language (PHP, C#, whatever). In all of these languages you can check the MySQL error code and error message. If your query failes because of the guest count >2, you will get error code `1048` and error message `"Column 'card_number' cannot be null"` - this is your explicit feedback. – Eugen Rieck Jan 12 '12 at 20:30
1

Ok. what about the ROLLBACK thing? can i use it ? – Jan 12 '12 at 20:32
No need to ROLLBACK - the insert never took place. For a discussion of Transactions (COMMIT, ROLLBACK and friends) this is not the place. – Eugen Rieck Jan 12 '12 at 20:33
Anyway, it works. Thankyou. I'd give you an "arrow up" if I could. – Jan 12 '12 at 20:43
@EugenRieck, sorry; I didn't see the `not null` on the table. – Ben Jan 12 '12 at 21:01

MySQL assertion-like constraint

1 Answers1

Linked