Add constraint to table creation in SQL

Question

I have to create a table Product with elements P, PType, PName, Price and Origin.

I need to have the constraint that all products with PType "Meat" must have "Europe" as Origin. I wanted to add the constraint in an alter table but I saw that using an alter table means modifying the original table after its creation, but I need to have this condition at the creation of the table. I don't know how to write this constraint, can someone give me a hint?

Answer 1

IN MySQL 8 (>= 8.0.16) You can use a CHECK CONSTRAINT

In older Versions you will need an INSERT/UPDATE TRIGGER

That said, you should also normalize your table and use a lookup (or reference/helper) table for an Origin id and a Type id.

CREATE TABLE Product 
( 
    P INT 
  , PType varchar(19)
  , PName varchar(20)
  , Price DECIMAL(8,2)
  , Origin varchar(50)
  , CONSTRAINT CHK_type CHECK ((Ptype = 'meat' AND Origin ='Europe') 
                           OR Ptype <> 'meat' )
);

INSERT INTO Product VALUES (1,'meat','Kobe Beef',10000.00,'Japan')

Check constraint 'CHK_type' is violated.

INSERT INTO Product VALUES (1,'apple','Breaburn',10.00,'New Zealand')

✓

db<>fiddle here