SQL Puzzle 1: Fiscal Year Tables

2020-02-26 (Wednesday) | 400 words (~2 minutes reading)

Notes to myself.

The first puzzle in SQL Puzzles is about adding strong constraints to ensure correctness of data in a table that looks like this:

CREATE TABLE fiscal_years (
	fiscal_year integer,
	start_date  date,
	end_date    date
)

Making all the columns NOT NULL is a good default first step as it avoids a large source of errors.

CREATE TABLE fiscal_years (
	fiscal_year integer NOT NULL,
	start_date  date    NOT NULL,
	end_date    date    NOT NULL
)

We can prevent other errors by adding UNIQUE constraints to each of the three columns, as we don’t want any shared values across rows for them: each fiscal year should only be described once, and no fiscal year can share a start date or end date with another.

CREATE TABLE fiscal_years (
	fiscal_year integer NOT NULL UNIQUE,
	start_date  date    NOT NULL UNIQUE,
	end_date    date    NOT NULL UNIQUE
)

It would also be easy and beneficial to ensure that the start dates and end dates are sane with a CHECK:

CREATE TABLE fiscal_years (
	fiscal_year integer NOT NULL UNIQUE,
	start_date  date    NOT NULL UNIQUE CHECK (start_date < end_date),
	end_date    date    NOT NULL UNIQUE CHECK (end_date > start_date)
)

These are simple checks but even this level of constraint is used too rarely in a lot of software projects.

Having said that, Celko then suggests a large, more complicated constraint that goes into so much detail that it seems to me to make the table pointless – if the rules for determining the start are this clear, then we don’t need this table and could just determine the fiscal year start and end times on the fly as needed:

	CONSTRAINT valid_start_date
	CHECK ((EXTRACT (YEAR FROM start_date) = fiscal_year - 1)
		AND (EXTRACT (MONTH FROM start_date) = 10)
		AND (EXTRACT (DAY FROM start_date) = 01)
		),
	# etc...

However, there is a simpler constraint that actually rules out a lot of errors by sanity-checking that the start and end dates produce a reasonable number of days for a fiscal year:

CHECK (age(end_date, start_date) = INTERVAL '359 DAYS')

Which gives a final CREATE TABLE statement like this:

CREATE TABLE fiscal_years (
	fiscal_year integer NOT NULL UNIQUE,
	start_date  date    NOT NULL UNIQUE CHECK (start_date < end_date),
	end_date    date    NOT NULL UNIQUE CHECK (end_date > start_date),
	CONSTRAINT valid_number_days
		CHECK (age(end_date, start_date) = INTERVAL '359 DAYS')
)

Joe Celko’s SQL Puzzles & Answers


Tech mentioned