1

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?

1
  • Do you want: option 1 - if PType = "Meat" but Origin != "Europe" then fail the query; option 2 - if PType = "Meat" then set Origin = "Europe" despite of assigned value (no fail).
    – Akina
    Apr 9 '21 at 21:23
3

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

2
  • Thank you so much, it has been very helpful! Apr 9 '21 at 23:21
  • The constraint can be simplified to CHECK (Origin = 'Europe' OR Ptype <> 'meat') but I admit that your version is much more clear. Apr 10 '21 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.