SQL puzzles and answers读书笔记——财年表
2010-08-16 22:10 知行思新 阅读(1042) 评论(0) 编辑 收藏 举报最近开始阅读数据库大师Joe Celko的《SQL PUZZLES AND ANSWERS》一书。这本书每篇都是一个SQL谜题,涉及数据库设计、开发。我看的是英文第二版,在这里做个读书笔记,顺便用T-SQL和PL/SQL来实现其中的代码。
谜题1——财年表
我们首先来创建一张表(以下代码在SQL Server 2008和Oracle上都能运行通过):
create table FiscalYearTable1 ( fiscal_year int, start_date date, end_date date );
这张表存储了每个财年的起始日期和结束日期。用以下查询能找出outside_date所属的财年:
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:
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:
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)