SQL puzzles and answers读书笔记——财年表

最近开始阅读数据库大师Joe Celko的《SQL PUZZLES AND ANSWERS》一书。这本书每篇都是一个SQL谜题，涉及数据库设计、开发。我看的是英文第二版，在这里做个读书笔记，顺便用T-SQL和PL/SQL来实现其中的代码。

谜题1——财年表

我们首先来创建一张表（以下代码在SQL Server 2008和Oracle上都能运行通过）：

?

1 2 3 4 5 6

create table FiscalYearTable1 (     fiscal_year int,     start_date date,     end_date date );

这张表存储了每个财年的起始日期和结束日期。用以下查询能找出outside_date所属的财年：

?

1 2 3 4 5 6

select     F1.fiscal_year from     FiscalYearTable1 as F1 where     outside_date between F1.start_date and F1.end_date

我们的谜题是：对于这张表加哪些约束能防止错误数据的输入？

讨论与方案

1. 首先能想到的是给每个列加上NOT NULL约束，因为在这张表中存放NULL值没有特别的意义和好处。

2. 作为SQL程序员很快能想到要加PRIMARY KEY约束。对于FiscalYearTable1表可以在fiscal_year列加PRIMARY KEY约束。同样start_date和end_date列也不允许重复值的出现，所以可以加上UNIQUE约束。

3. 约束CHECK(start_date < end_date)非常明显，但却容易被人遗忘。

4. 虽然有了上述约束，但还是无法避免如下的错误：

(2007, ‘2006-10-01’, '2007-09-30')

(2008, ‘2007-10-01’, '2008-09-30’)

(2009, ‘2008-10-01’, '2009-08-30’) <==出错了！

(2010, ‘2009-10-01’, '2010-09-30’)

对于如上start_date月日和end_date月日固定的财年表，可以加更为精确的约束。代码如下：

T-SQL：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create table FiscalYearTable1 (     fiscal_year int not null primary key,     start_date date not null,     constraint valid_start_date         check((year(start_date) = fiscal_year - 1)                 and (month(start_date) = 10)                 and (day(start_date) = 1)),     end_date date not null,     constraint valid_end_date         check((year(end_date) = fiscal_year)                 and (month(end_date) = 9)                 and (day(end_date) = 30)) );

PL/SQL：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create table FiscalYearTable1 (   fiscal_year int not null primary key,   start_date date not null,   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) = 1)),   end_date date not null,   constraint valid_end_date     check((extract(year from end_date) = fiscal_year)           and (extract(month from end_date) = 9)           and (extract(day from end_date) = 30)) );

5. 有些公司的财年计算方式和4中的不同，如以52周为一个财年。这种情况下就无法使用4中对start_date和end_date的约束了，我们可以加如下约束(52周 × 7天 = 364天)：

CHECK((end_date – start_date) = INTERVAL 364 DAYS)