Check constraint in MySQL

Question

Possible Duplicate:
Throw an error in a MySQL trigger

I have a table definition that uses CHECK but as MySQL don't support this feature I need to do something different. The idea is to use a trigger before INSERT or UPDATE on this table but I'm not sure how to do it.

Here is the table:

CREATE TABLE IF NOT EXISTS `smeii_media` (
    `id` INTEGER AUTO_INCREMENT,
    `type` VARCHAR(5) NOT NULL,
    `title` VARCHAR(100) NOT NULL,
    `description` VARCHAR(500),
    `path` VARCHAR(100) NOT NULL,
    UNIQUE(`path`),
    PRIMARY KEY(`id`),
    CONSTRAINT chk_media CHECK (`type`='audio' OR `type`='image' OR `type`='video')
) ENGINE=InnoDB;

And here the trigger:

DELIMITER $$

CREATE TRIGGER smeii_media_briu BEFORE INSERT OR UPDATE FOR EACH ROW
BEGIN
    IF NEW.type != 'audio' AND NEW.type != 'image' AND NEW.type != 'video'
    THEN

    END IF;
END;

DELIMITER $$

What can I put inside the if-block to throw an error?

What are you trying to select for / limit? Seems like you could rearrange the IF clause to do this. (rather than throw an error) — ethrbunny, Oct 24 '12 at 16:45
I want type value to be audio, image or video and I would like to throw an error to show it in the website. — David Moreno García, Oct 24 '12 at 16:48
possible duplicate of [Throw an error in a MySQL trigger](http://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger) and [How to abort INSERT operation in MySql trigger?](http://stackoverflow.com/questions/2538786/how-to-abort-insert-operation-in-mysql-trigger) — Jocelyn, Oct 24 '12 at 16:48
Yes, kind of. I'm going to accept the first answer instead of close the thread as it could be useful for someone. Thank you. — David Moreno García, Oct 24 '12 at 16:57
I voted to reopen. There's another solution that's not a suitable answer for the linked "duplicate", but will work fine for this question. (Foreign key to another table.) — Mike Sherrill 'Cat Recall', Feb 05 '15 at 22:17

score 1 · Accepted Answer · answered Oct 24 '12 at 16:51

1

Instead of throwing an error in the trigger (which isn't really supported) what if you make the clause:

BEGIN
IF NEW.type = 'audio' OR NEW.type = 'image' OR NEW.type = 'video'
THEN

END IF;

And ignore everything else.

answered Oct 24 '12 at 16:51

ethrbunny

10,379
9
69
131

Check constraint in MySQL

1 Answers1