MySQL Check Constraints to the Rescue

   arrow

You as a MySQL administrator/developer are really excited about cloning in MySQL 8.0.17.  You are reading all the recent blogs. You know that quickly getting a replica up and running is quite important to your dev team.

Besides, ‘cloning’ reminds you of the old classic film ‘Sleeper’ where the actors clone a new leader from the dead leader’s nose. 

Thinking of all this puts you in great spirits. Moving from MySQL 5.7 to MySQL 8.0 is high on your agenda. 

Suddenly, the CEO walks up to your desk. “We lost millions in sales last quarter,” he yells. “It’s your fault. We sold all of our items at the discounted price rather than the actual price. You need to fix this today if not sooner.” 

With that, the CEO reminds you of another reason to move to MySQL 8.0 : check constraints, which came out in 8.0.16. 

You know that ensuring that data is accurate is a big task. Someone may have put logic to keep prices in check in the code, but who knows? It is better to add this logic to the database. Constraints in a database will be more permanent than if constraints live in application code that changes frequently. In the database, there is just one place for constraints and that is on the object layer.

You get to work. You design three check constraints to ensure that your CEO is happy and you will never sell products at the wrong price again. You notice that in the old product table, there were no constraints on the price columns in the table.  The values for the ‘price’ column were often lower than the ‘sales_price’ column.

The table constraint below sets three constraints. The last constraint on the table product below in bold ensures that the regular price is always higher than the sales price. The error that occurs below on adding a new product or updating an existing product shows that the third check constraint is being applied:

CREATE TABLE product
(
product_no INTEGER,
name TEXT,
price NUMERIC CHECK (price > 0),
sale_price NUMERIC CHECK (sale_price > 0),
CHECK (price > sale_price)
);

mysql> INSERT INTO product VALUES (1, ‘teeth-cleaning-gum’, 10, 10);

ERROR 3819 (HY000): Check constraint ‘products_chk_3’ is violated.

 

The CEO wanders back asking to see your work to prevent another financial loss. After you show him your work, he looks more positive. His only comment is ‘when is your team moving to MySQL 8.0?’  You nod, saying that with all the improvements to the MySQL code the time to move to MySQL 8.0 is now.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s