0

I am trying to figure out how to properly migrate a sql server database into a mysql database. Everything went OK beside the check constraints (and on update cascade).

Is there any way or tool to automatic transform CHECK constraint into triggers?

ALTER TABLE customer
ADD Constraint CK_customer_Sex
CHECK (Sex= 'M' OR Sex= 'F' );

into something like

CREATE TRIGGER `CK_customer_Sex` BEFORE INSERT ON `Sex`
    FOR EACH ROW
    BEGIN
        IF SEX <> 'M' or SEX <> 'F' THEN
        END IF;
END;

Unfortunately I can't manage to get the trigger working with error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 5

Wim
  • 11
  • 1
  • 4
  • I don't know mysql but you have an empty code block here. Perhaps you need to actually do something inside there? – Sean Lange Dec 21 '16 at 20:34
  • You can't have an empty `IF THEN END IF`; even if you could, that trigger would do nothing to enforce the constraint. You need to "throw and error" in the if block; I'm not familiar with the exact syntax myself though. [This answer](http://stackoverflow.com/a/7189396/4104224) looks promising though. – Uueerdo Dec 21 '16 at 20:34
  • 1
    Your trigger also has a logic problem, this is always true `IF SEX <> 'M' or SEX <> 'F'` – James Z Dec 21 '16 at 20:49
  • Thanks for the comments so far, I need to dive deeper into the whole 'create trigger' thing here. – Wim Dec 21 '16 at 20:52
  • Given that it's 2016, hard-locking gender on `M` or `F` is pretty backwards thinking. Many localities are offering people things for "not specified" like `X`, among other things. Additionally this is a good opportunity to move this sort of validation to the application layer where it belongs. – tadman Dec 21 '16 at 21:23

0 Answers0