MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


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))
);
INSERT INTO orders VALUES (1, 'i', 'safadf'), (2, 'i', 'xcvzxv'), (3, 'e', NULL);

Records: 3  Duplicates: 0  Warnings: 0


SELECT * FROM orders;


order_no
order_type
engine_description


1
i
safadf

2
i
xcvzxv

3
e
null


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

Check constraint 'ot_ed_ck' is violated.


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


order_no
order_type
engine_description


1
i
safadf

2
i
xcvzxv

3
e
null

5
i
The default