Constraints设置练习
对于报表的创建来说，难点无非有这样几个：1、考虑是否需要分区、建簇等；2、设置表的存储空间参数；3、设置完整性约束。其中第1、2点一般都针对大型的数据库，而第3点是最常用到的。所以最近学习一下如何使用constraints来实现一些约束，直接在源头杜绝数据错误。
1 . FISCAL YEAR TABLES
Let's write some CREATE TABLE statements that are as complete aspossible. This little exercise is important because SQL is a declarativelanguage and you need to learn how to specify things in the databaseinstead of in the code.
The table looks like this:
CREATE TABLE FiscalYearTable1
(fiscal_year INTEGER,
start_date DATE,
end_date DATE);
It stores date ranges for determining what fiscal year any given datebelongs to. For example, the federal government runs its fiscal year fromOctober 1 until the end of September. The scalar subquery you woulduse to do this table lookup is:
(SELECT F1.fiscal_year
FROM FiscalYearTable1 AS F1
WHERE outside_date BETWEEN F1.start_date AND F1.end_date)
Your assignment is to add all the constraints you can think of to thetable to guarantee that it contains only correct information.
While vendors all have different date and time functions, let's assumethat all we have is the SQL-92 temporal arithmetic and the function:
EXTRACT ([YEAR | MONTH | DAY] FROM <date expression>), whichreturns an integer that represents a field within a date.
Answer #1
1. First things first; make all the columns NOT NULL since thereis no good reason to allow them to be NULL.
2. Most SQL programmers immediately think in terms of addinga PRIMARY KEY, so you might add the constraint
PRIMARYKEY (fiscal_year, start_date, end_date)
because the fiscal year isreally another name for the pair (start_date, end_date). This isnot enough, because it would allow this sort of error:
(1995, '1994-10-01', '1995-09-30')
(1996, '1995-10-01', '1996-08-30') <== error!
(1997, '1996-10-01', '1997-09-30')
(1998, '1997-10-01', '1997-09-30')
You could continue along the same lines and fix some problemsby adding the constraints UNIQUE (fiscal_year),UNIQUE (start_date), and UNIQUE (end_date), since we donot want duplicate dates in any of those columns.
3. The constraint that almost everyone forgets to add because it isso obvious is:
CHECK (start_date < end_date) or CHECK (start_date <=end_date), as is appropriate.
4. A better way would be to use the constraint PRIMARY KEY(fiscal_year) as before, but then since the start and end datesare the same within each year, you could use constraints onthose column declarations:
CREATE TABLE FiscalYearTable1
(fiscal_year INTEGER 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) = 01)),
end_date DATE NOT NULL,
CONSTRAINT valid_end_date
CHECK ((EXTRACT (YEAR FROM end_date) = fiscal_year)
AND (EXTRACT (MONTH FROM end_date) = 09)
AND (EXTRACT (DAY FROM end_date) = 30)));
You could argue for making each predicate a separate constraintto give more detailed error messages. The predicates onthe year components of the start_date and end_date columnsalso guarantee uniqueness because they are derived from theunique fiscal year.
5. Unfortunately, this method does not work for all companies.Many companies have an elaborate set of rules that involve takinginto account the weeks, weekends, and weekdays involved.They do this to arrive at exactly 360 days or 52 weeks in theiraccounting year. In fact, there is a fairly standard accountingpractice of using a “4 weeks, 4 weeks, 5 weeks” quarter withsome fudging at the end of the year; you can have a leftoverweek between 3 and 11 days. The answer is a FiscalMonthtable along the same lines as this FiscalYears example.
A constraint that will work surprisingly well for such cases is:
CHECK ((end_date - start_date) = INTERVAL 359 DAYS)
where you adjust the number of days to fit your rules (i.e., 52 weeks * 7 days = 364 days). If the rules allow some variation in the size of the fiscalyear, then replace the equality test with a BETWEEN predicate.
Now, true confession time. When I have to load such a table in adatabase, I get out my spreadsheet and build a table using the built-intemporal functions. Spreadsheets have much better temporal functionsthan databases, and there is a good chance that the accountingdepartment already has the fiscal calendar in a spreadsheet.