Creating a table with CHECK constraints in MySql workbench

Question

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?

Vérace · Answer 1 · 2020-12-05T12:56:31.390

This can be done very elegantly using CHECK constraints in MySQL >= 8.0.16 as follows (fiddle available here):

Your table (changed slightly)

CREATE TABLE IF NOT EXISTS orders 
(
  order_no INT,
  order_type CHAR(8) CHECK (order_type IN ('e', 'i')) ,
  engine_description VARCHAR(45) DEFAULT 'The default',  -- NOT NULL constraint removed
  CONSTRAINT ot_ed_ck 
    CHECK ((order_type = 'i') OR (order_type = 'e' AND engine_description IS NULL))
);

Populate it:

INSERT INTO orders 
VALUES 
(1, 'i', 'safadf'), (2, 'i', 'xcvzxv'), (3, 'e', NULL);

Then

SELECT * FROM orders;

Result:

order_no    order_type  engine_description
       1             i              safadf
       2             i              xcvzxv
       3             e                null

So far, so good.

Then we try to do this:

INSERT INTO orders VALUES ('4', 'e', 'xxxxx');

and we get the error message:

Check constraint 'ot_ed_ck' is violated.

Which is exactly what we want!

To cover the case of or else a value will be added? in the question, by which I presume you mean a DEFAULT - if we specifically exclude engine_description from the list of values to be INSERTed as follows:

INSERT INTO orders (order_no, order_type) VALUES (5, 'i');

and we SELECT:

SELECT * FROM orders

We obtain:

order_no    order_type  engine_description
      1              i              safadf
      2              i              xcvzxv
      3              e                null  
      5              i         The default   <<----- Default!

I would strongly urge you to upgrade to MySQL 8 - it's now at version 8.0.22, so any major version change wrinkles will have been ironed out by now - you get all sorts of goodies - like CHECK constraints that I just outlined, COMMON TABLE EXPRESSIONs (available from 8.0.1, aka the WITH clause) and also GENERATED columns (5.7.5).

If you cannot upgrade to version 8, then you'll have to use TRIGGERs (as per @nbk's answer), but you asked for constraints so that's what I supplied.

Also, what exactly do you mean by:

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

If you mean constraints between two fields in the same table (intra-table), you have the example above, if you mean between two tables (inter-table), then on any server (bar Firebird), you'll have to use TRIGGERs!

Finally, a word of advice - do NOT use SQL keywords as table or column names (in this case Order)- it's confusing, makes debugging more difficult and is not good practice. Normally, I use singular table names, but I make an exception (obviously) for ORDERs - a table is a set and should be singular (IMHO).

p.s. welcome to the forum!

nbk · Accepted Answer · 2020-12-05T12:31:14.987

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

Creating a table with CHECK constraints in MySql workbench

2 Answers2