woensdag 28 januari 2015

Why I like PostgreSQL more than MySQL: conditional NOT NULL constraints

Sometimes you want some fields of a record to be filled out, but only if some condition is met. For example, if a user registers as a company, you'll want his company name.

In MySQL you can do this using a trigger, which works just fine but in PostgreSQL you can do it in a much more compact way, using CHECK constraints.

In this example, all records added to foo must meet the requirement that is_admin must be false if the firstname or lastname are left empty. If is_admin is true then the firstname and lastname may not be null.

CREATE TABLE foo (id INTEGER, firstname VARCHAR(100), lastname VARCHAR(100), is_admin BOOLEAN NOT NULL DEFAULT 'f');
ALTER TABLE foo ADD CONSTRAINT adminfields CHECK (is_admin = 'f' OR (is_admin='t' and firstname IS NOT NULL AND lastname IS NOT NULL));

These inserts meet the requirements:

INSERT INTO foo (id) VALUES (1);
INSERT INTO foo (id, firstname, lastname, is_admin) VALUES (1,'kees','pekinees','t');

And this one will fail:
INSERT INTO foo (id, is_admin) VALUES (2, 't');

You can expand on this by making sure the name is not just NOT NULL, but actually contains sane data, using a regexp.

The major advantage of CHECK over triggers is that the CHECK shows up in the CREATE statement of the table, giving you single overview of the table's behaviour.

Again; there is nothing really wrong with triggers, this is just more convenient.