Add constraint to table creation in SQL

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply