MySQL Workbench – Creating a Table with CHECK Constraints in MySQL Workbench

MySQLmysql-5.5mysql-5.6mysql-5.7mysql-workbench

I created a table ORDER using Mysql Workbench as follows:

CREATE TABLE IF NOT EXISTS `rr`.`ORDER` (
  `Order No.` INT UNSIGNED NOT NULL COMMENT 'The order of the inspected equipment',
  `Order_Type` CHAR(8) NOT NULL COMMENT 'External or Internal work',
  `Engine_Description` VARCHAR(45) NOT NULL COMMENT 'Description of the Engine',
  `Equipment_Quantity` INT UNSIGNED NOT NULL COMMENT ' Number of the inspected equipment',
  `Required_Task` VARCHAR(8) NOT NULL COMMENT 'Whether Repair or Overhaul',
  PRIMARY KEY (`Order No.`))
ENGINE = InnoDB
COMMENT = '\n';

So I need to know how to make a constraint where if the value of the Order_Type field is 'External' then the value of automatically the value of Engine_Description will be NULL or else a value will be added?

Also, if I want to do the same for two different entities?

Best Answer

As i said in my comment, use Triggers for that purpose

Like shown here, you have add every constraint as if clause to your trigger., because mysql 5.x doesn't support CHECK constraints

Also note that Order is a reserved word, and you shouldn't use them in table or column names else you have always to use Backticks in every Query

Your field Engine_Description is declared as NOT NULL so it can't be set to NULL i set it so to an empty descrition

CREATE TABLE IF NOT EXISTS `ORDER` (
  `Order No.` INT UNSIGNED NOT NULL COMMENT 'The order of the inspected equipment',
  `Order_Type` CHAR(8) NOT NULL COMMENT 'External or Internal work',
  `Engine_Description` VARCHAR(45) NOT NULL COMMENT 'Description of the Engine',
  `Equipment_Quantity` INT UNSIGNED NOT NULL COMMENT ' Number of the inspected equipment',
  `Required_Task` VARCHAR(8) NOT NULL COMMENT 'Whether Repair or Overhaul',
  PRIMARY KEY (`Order No.`))
ENGINE = InnoDB
COMMENT = '\n';

CREATE TRIGGER before_orders_insert
BEFORE INSERT
ON `ORDER` FOR EACH ROW
BEGIN
 IF NEW.Order_Type  ='External' THEN
     SET new.`Engine_Description` = '';
 END IF;

END;

CREATE TRIGGER before_orders_UPDATE
BEFORE UPDATE
ON `ORDER` FOR EACH ROW
BEGIN
 IF NEW.Order_Type  ='External' THEN
     SET new.`Engine_Description` = '';
 END IF;

EN

INSERT INTO `ORDER` VALUES (1,'External','test descrition',1,'Repair')
,(2,'Internal','test descrition',2,'Repair')

SELECT * FROM `ORDER`

Order No. | Order_Type | Engine_Description | Equipment_Quantity | Required_Task
--------: | :--------- | :----------------- | -----------------: | :------------
        1 | External   |                    |                  1 | Repair       
        2 | Internal   | test descrition    |                  2 | Repair

db<>fiddle here

Related Question