Google+ Followers

woensdag 26 februari 2014

Why I like PostgreSQL more than MySQL: Check constraints

Databases can basically only store strings and numbers. Your business however has a frightening number of rules an regulations about what the data should look like.

A sales price for example must always be more than zero. A discount must always be between zero and the salesprice. A tax rate is always between 0% and 100%. The number of units per package is always one or more. A product cannot be marked as sold-out if backorders are allowed. And so on...

In MySQL you can do this with triggers, which works, but is hardly elegant, let alone easy to manage.

In PostgreSQL you can use a CHECK constraint on s single field:


DROP TABLE IF EXISTS foo;
CREATE TABLE foo (id INT, price NUMERIC(10,2) CONSTRAINT legal_price_check CHECK  (price>0));
INSERT INTO foo (id, price) VALUES (1,0);

"ERROR:  new row for relation "foo" violates check constraint "legal_price_check""


Or on he table as a whole:
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (id INT, price NUMERIC(10,2), weight NUMERIC(5,2), shippingcost NUMERIC(4,2),
 CONSTRAINT legal_shipping_cost CHECK  ((weight<= 100 AND shippingcost=0) OR (weight>100 and shippingcost>0)));

INSERT INTO foo (id, price, weight, shippingcost) VALUES (1,10, 90,0);
INSERT INTO foo (id, price, weight, shippingcost) VALUES (1,10, 90,10);

The first record works, the second is denied because the shippingcost should be zero if the weight is below 100.