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?
IN MySQL 8 (>= 8.0.16) You can use a
In older Versions you will need an
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')✓