SQLパズル 1問目

1.問題：会計年度テーブル

CREATE TABLE文に関する問題です．

CREATE TABLE FiscalYearTable1
(fiscal_year INTEGER,
 start_date DATE,
 end_date DATE);

　FiscalYearTable1：会計年度テーブル
　fiscal_year：会計年度
　start_date：年度開始日
　end_date：年度終了日
このFiscalTable1テーブルには，各会計年度がいつから始まりいつに終わるのか，その日付の範囲が格納される．ここでは，米国政府の会計年度（10月1日～9月30日）を例に私用する．このテーブルに対し正しい情報だけをもつように自分で考えて制約をもれなく設定しなさい，というのがこの問題です．

2.導出過程と解答

2.1 NOT NULL

とりあえず，全てのカラムに「NOT NULL」制約を付与します．
会計年度がNULLなのに，年度開始日と年度終了日が存在するのは奇妙ですし，その逆もまた然りです．

2.2 PRIMARY KEY

どのカラムに主キーを設定するかについてですが，同じ年に2回も会計年度が設定されることはないので，会計年度のカラムを主キーにします．
またstart_dateとend_dateについて，型がDATEでYYYYも含まれており，会計年度が各年１つであることからここも各年で一意でなければいけません．なので，この2つのカラムにはUNIQUEを設定します．

2.3 start_dateとend_dateの制約

今回の会計年度テーブルでは米国政府の会計年度を例にとっているので，開始日は必ず10月1日から，終了日は9月30日であることが確定しています．また，開始日のYYYYは会計年度から1を引いたもの，終了日のYYYYは会計年度のままとなるはずです．これを制約として記述すれば問題ないでしょう．
あとは，終了日が開始日のあとになるようしなければいけませんが，上記の制約を設定すれば自動的に満たされるので今回は設定する必要はないでしょう．

2.4 解答

今までの2.1から2.3の内容を踏まえると以下の回答になります．

CREATE TABLE FiscalYearTable1
(fiscal_year INTEGER NOUT NULL PRIMARY KEY,
 start_date DATE NOT NULL UNIQUE,
 CONSTRAINT start_date_condition
 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 UNIQUE,
 CONSTRAINT end_date_condition
 CHECK((EXTRACT(YEAR FROM end_date) = fiscal_year)
        AND (EXTRACT(MONTH FROM end_date) = 09)
        AND (EXTRACT(DAY FROM end_date) = 30)));