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.
?
?
?