■データ型
- "SQLデータ型" を参考にしてください。
■制約
- 制約によってテーブル内のデータを自由に制御することができます。 制約に違反するデータを列に格納しようとすると、エラーとなります。
説明 列制約 表制約 DEFAULT 列の省略時値を指定します。(通常、これを制約とはいいません) DEFAULT CURRENT DATE - NOT NULL 列が NULL 値をとらないことを指定します。 NOT NULL - 一意性 列あるいは列のグループに含まれるデータが、テーブル内のすべての行で一意であることを確実にします。 UNIQUE UNIQUE(ano, bno) 主キー 一意性制約と NOT-NULL 制約を組み合わせたものです。この制約は、表にひとつしか設定できません。 PRIMARY KEY PRIMARY KEY(ano, bno) 検査 特定の列の値が任意の式を満たすように指定できます。 CHECK(price > 0) CHECK(price > discounted_price AND discounted_price > 0) 外部キー 列 (または列のグループ) の値が、他のテーブルの行の値と一致しなければならないことを指定します。 REFERENCES other_table(ano) FOREIGN KEY(pno, qno) REFERENCES other_table(ano, bno)
- NOT NULL 制約と DEFAULT 制約以外には、制約名をつけることができます。
CONSTRAINT 制約名 制約
■外部キー制約
- 外部キー制約は、列 (または列のグループ) の値が、他のテーブルの行の値と一致しなければならないことを指定します。 これによって関連する 2 つのテーブルの参照の整合性が維持されます。
- したがって、外部キー制約のことを、参照整合性制約ともよびます。
- 外部キー制約の形式
FOREIGN KEY ( 列名 [, ... ] ) REFERENCES 主表名 [ ( キー名 [, ... ] ) ] [ ON DELETE action ] [ ON UPDATE action ] } action: NO ACTION / RESTRICT CASCADE SET NULL SET DEFAULT
■例題
- 担当者表(employee)をみてみます。
- 担当者表(employee)に、つぎの制約をつけます。このとき、担当者表(employee)がすでにある場合は、"DROP TABLE employee;" コマンドを使って削除してください。
- 担当者ID(emp_id)列に、主キー制約
- 部門ID(dept_id)列に、NOT NULL制約と、外部キー制約(部門表(department)の主キーに割当て)
- 担当者名列(emp_name)に、NOT NULL制約
- 入社年月日列(hiredate)に、NOT NULL制約
- 性別列(sex)に、NOT NULL制約と、検査制約(値は 1 または 2)
- 給与額列(sal)に、NOT NULL制約
- 今回は、すべて列制約で作成します。
DROP TABLE employee CREATE TABLE employee ( emp_id INT NOT NULL PRIMARY KEY , dept_id INT NOT NULL REFERENCES department , emp_name VARCHAR(20) NOT NULL , birthday DATE , hiredate DATE NOT NULL , sex INT NOT NULL CHECK(sex IN (1, 2)) , sal NUMERIC(9,2) NOT NULL )結果DB20000I SQL コマンドが正常に終了しました。 C:\DB2>db2 describe TABLE employee タイプ・ 列名 スキーマ タイプ名 長さ 位取り NULL ------------------------------ --------- ------------------ -------- ----- ------ EMP_ID SYSIBM INTEGER 4 0 いいえ DEPT_ID SYSIBM INTEGER 4 0 いいえ EMP_NAME SYSIBM VARCHAR 20 0 いいえ BIRTHDAY SYSIBM DATE 4 0 はい HIREDATE SYSIBM DATE 4 0 いいえ SEX SYSIBM INTEGER 4 0 いいえ SAL SYSIBM DECIMAL 9 2 いいえ 7 レコードが選択されました。 C:\DB2>db2 describe indexes for TABLE employee 索引スキーマ 索引名 ユニークの規則 列の数 -------------- ------------------ -------------- -------------- SYSIBM SQL050429102202430 P 1 1 レコードが選択されました。 - いろいろ試します。
□正しいデータの挿入 => INSERT INTO employee VALUES(100, 20, '丸野 和夫', null, '2004-04-04', 1, 2000); DB20000I SQL コマンドが正常に終了しました。 => SELECT * FROM employee ORDER BY emp_id; EMP_ID DEPT_ID EMP_NAME BIRTHDAY HIREDATE SEX SAL ----------- ----------- -------------------- ---------- ---------- ----------- ----------- 100 20 丸野 和夫 - 2004-04-04 1 2000.00 1 レコードが選択されました。 □重複した主キーの挿入 => INSERT INTO employee VALUES(100, 30, '山田 真弓', null, '2004-04-04', 2, 2200); DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL ステートメントとして処理されました。 SQL 処理中に、そのコマンドが返されました。 SQL0803N "1" で識別される主キー、ユニーク制約、またはユニーク索引を持つ表 "ADMINISTRATOR.EMPLOYEES" の列に重複行を作成できないため、 INSERT ステートメント、UPDATE ステートメントの 1 つ以上の値、または DELETE ステートメントによって行われた外部キーの更新が無効です。 SQLSTATE=23505 □重複を直す => INSERT INTO employee VALUES(110, 30, '山田 真弓', null, '2004-04-04', 2, 2200); => SELECT * FROM employee ORDER BY emp_id; EMP_ID DEPT_ID EMP_NAME BIRTHDAY HIREDATE SEX SAL ----------- ----------- -------------------- ---------- ---------- ----------- ----------- 100 20 丸野 和夫 - 2004-04-04 1 2000.00 110 30 山田 真弓 - 2004-04-04 2 2200.00 2 レコードが選択されました。 □許されない外部キーを使用 => INSERT INTO employee VALUES(120, 100, '鈴木 浩二', null, '2004-04-04', 1, 2000); DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL ステートメントとして処理されました。 SQL 処理中に、そのコマンドが返されました。 SQL0530N FOREIGN KEY "ADMINISTRATOR.EMPLOYEES.SQL050426145117420" の挿入または更新値が親表の親キーと同じではありません。 SQLSTATE=23503 □入社年月日(hiredate)にNULL値を設定 => INSERT INTO employee VALUES(120, 10, '鈴木 浩二', null, null, 1, 2000); DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL ステートメントとして処理されました。 SQL 処理中に、そのコマンドが返されました。 SQL0407N NULL 値の NOT NULL 列 "TBSPACEID=2, TABLEID=4, COLNO=4" への割り当ては許されていません。 SQLSTATE=23502 □性別(sex)に許されない値を設定 => INSERT INTO employee VALUES(120, 10, '鈴木 浩二', null, '2004-04-04', 3, 2000); DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL ステートメントとして処理されました。 SQL 処理中に、そのコマンドが返されました。 SQL0545N 行がチェック制約 "ADMINISTRATOR.EMPLOYEES.SQL050426145117430" を満たしていないために、要求された処理は実行されません。 SQLSTATE=23513 □部門表(department)から、行を削除 => DELETE FROM department WHERE dept_id = 10; DB20000I SQL コマンドが正常に終了しました。 □部門表(department)から、担当者表が関係ついている行を削除 => DELETE FROM department WHERE dept_id = 20; DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL ステートメントとして処理されました。 SQL 処理中に、そのコマンドが返されました。 SQL0532N リレーションシップ "ADMINISTRATOR.EMPLOYEES.SQL050426145117420" が削除を制限しているので、親行を削除できません。 SQLSTATE=23504
■実習
- レストラン･データベース(restaurant)に、以下の表を作ります。("CreateRestaurantTables.sql")
なお、データは、"InsertRestaurantData.db2.sql"を使って、各表にデータを挿入してください。また、挿入した後のデータ数のチェックには、"CheckRestaurantData.sql"を使ってください。- 商品表(goods)
- 食材表(food)
- レシピ表(recipe)
- 仕入先表(supplier)
□スケルトン："CreateRestaurantTables.skeleton.db2.sql"
-- GOODS DROP TABLE goods; CREATE TABLE goods ( -- -- ここにコードを記述してください。 -- ); -- RECIPE DROP TABLE recipe; -- -- ここにコードを記述してください。 -- ); -- FOOD DROP TABLE food; CREATE TABLE food ( -- -- ここにコードを記述してください。 -- ); -- SUPPLIER DROP TABLE supplier; CREATE TABLE supplier ( supplier_cd CHAR(6) NOT NULL , supplier_name VARCHAR(40) NOT NULL , addr VARCHAR(60) , post_num VARCHAR(8) , tel VARCHAR(20) , fax VARCHAR(20) , constraint pkey_supplier PRIMARY KEY (supplier_cd) );□結果$ db2 -tvf InsertRestaurantData.db2.sql : $ db2 -tvf CheckRestaurantData.db2.sql TABLE COUNT -------- ----------- supplier 15 goods 22 recipe 40 food 50 4 レコードが選択されました。
■ALTER TABLE 文の基本
- 一般形式(基本)
Top
■例題
■実習
- レストラン･データベース(restaurant)に、以下の外部キー制約を作ります。("CreateRestaurantTables.sql")
- 食材表(food): 仕入先コード(supplier_cd)列に対して、仕入先表(supplier)の主キー
- レシピ表(recipe): 商品コード(goods_cd)列に対して、商品表(goods)の主キー、および 食材コード(food_cd)列に対して、食材表(food)の主キー
図 レストラン・データベース□結果
=> ALTER TABLE food add foreign key(supplier_cd) references supplier; DB20000I SQL コマンドが正常に終了しました。 => DESCRIBE TABLE food タイプ・ 列名 スキーマ タイプ名 長さ 位取り NULL ------------------------------ --------- ------------------ -------- ----- ------ FOOD_CD SYSIBM CHARACTER 6 0 いいえ FOOD_NAME SYSIBM VARCHAR 40 0 いいえ SUPPLIER_CD SYSIBM CHARACTER 6 0 いいえ SUPPLIER_COST SYSIBM INTEGER 4 0 いいえ SUPPLIER_UNIT SYSIBM VARCHAR 10 0 いいえ CONVERT_COST SYSIBM INTEGER 4 0 いいえ RECIPE_UNIT SYSIBM VARCHAR 10 0 いいえ 7 レコードが選択されました。 => ALTER TABLE recipe add foreign key(goods_cd) references goods; => ALTER TABLE recipe add foreign key(food_cd) references food; => describe TABLE recipe タイプ・ 列名 スキーマ タイプ名 長さ 位取り NULL ------------------------------ --------- ------------------ -------- ----- ------ GOODS_CD SYSIBM CHARACTER 6 0 いいえ FOOD_CD SYSIBM CHARACTER 6 0 いいえ NECESSARY_NUM SYSIBM DECIMAL 7 2 いいえ 3 レコードが選択されました。 db2 => describe indexes for TABLE recipe 索引スキーマ 索引名 ユニークの規則 列の数 -------------- ------------------ -------------- -------------- ADMINISTRATOR PKEY_RECIPE P 2 1 レコードが選択されました。 - 作成したレストラン･データベース(restaurant)の表に対して、いくつか、照会します。
■DROP TABLE 文の基本
- 一般形式(基本)
DROP TABLE 表名Top
■例題
- 担当者表(employee)と部門表(department)を削除します。
DROP TABLE employee, department
結果 - いろいろ試します。
■構文
- CREATE TABLE文
>>-CREATE--TABLE-- table-name ------------------------------------>
>--+-| element-list |----------------------------+--*----------->
>--+----------------------------------------------+--*----------><
'-IN-- tablespace-name1 --| tablespace-options |-'
element-list:
.-,----------------------------.
V |
|--(----+-| column-definition |------+-+--)---------------------|
+-| unique-constraint |------+
+-| referential-constraint |-+
'-| check-constraint |-------'
tablespace-options:
|--+---------------------------------+-------------------------->
| |
'-INDEX IN-- tablespace-name2 ----'
>--+-----------------------------+--------------------------------|
'-LONG IN-- tablespace-name3 -'
column-definition:
|-- column-name --+--------------------+-------------------------->
| |
'-| data-type |------'
>--+--------------------+---------------------------------------|
'-| column-options |-'
column-options:
.--------------------------------------------------------------------------------------------------.
V |
|----+----------------------------------------------------------------------------------------------+-+--|
+-NOT NULL-------------------------------------------------------------------------------------+
| |
+-| lob-options |------------------------------------------------------------------------------+
| |
+-| datalink-options |-------------------------------------------------------------------------+
| |
+-SCOPE--+- typed-table-name -+----------------------------------------------------------------+
| '- typed-view-name --' |
+-+-------------------------------+-+-+-PRIMARY KEY-+----------------------------------------+-+
| '-CONSTRAINT-- constraint-name -' | '-UNIQUE------' | |
| +-| references-clause |----------------------------------+ |
| '-CHECK--(- check-condition -)-| constraint-attributes |-' |
+-| generated-column-spec |--------------------------------------------------------------------+
| |
+-INLINE LENGTH-- integer ---------------------------------------------------------------------+
'-COMPRESS SYSTEM DEFAULT----------------------------------------------------------------------'
data-type:
|--+-SMALLINT---------------------------------------------------------------+--|
+-+-INTEGER-+------------------------------------------------------------+
| '-INT-----' |
+-BIGINT-----------------------------------------------------------------+
+-+-FLOAT--+---------------+-+-------------------------------------------+
| | '-(- integer -)-' | |
| +-REAL---------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+----' |
+-+-DECIMAL-+--+-------------------------------+-------------------------+
| +-DEC-----+ '-(- integer -+------------+--)-' |
| +-NUMERIC-+ '- ,integer -' |
| '-NUM-----' |
+-+-+-CHARACTER-+--+-------------+------------+--+---------------------+-+
| | '-CHAR------' '- (integer) -' | | | |
| +-+-VARCHAR----------------+--(- integer -)-+ '--------FOR BIT DATA-' |
| | '-+-CHARACTER-+--VARYING-' | |
| | '-CHAR------' | |
| '-LONG VARCHAR------------------------------' |
+-+-+-BLOB----------------+---------+--+------------------------+--------+
| | '-BINARY LARGE OBJECT-' | '-(- integer -+-----+--)-' |
| +-+-CLOB------------------------+-+ +- K -+ |
| | '-+-CHARACTER-+--LARGE OBJECT-' | +- M -+ |
| | '-CHAR------' | '- G -' |
| '-DBCLOB--------------------------' |
+-GRAPHIC--+-------------+-----------------------------------------------+
| '- (integer) -' |
+-VARGRAPHIC-- (integer) ------------------------------------------------+
+-LONG VARGRAPHIC--------------------------------------------------------+
+-DATE-------------------------------------------------------------------+
+-TIME-------------------------------------------------------------------+
+-TIMESTAMP--------------------------------------------------------------+
+-DATALINK--+---------------+--------------------------------------------+
| '-(-- integer --)-' |
+- distinct-type-name ---------------------------------------------------+
+- structured-type-name -------------------------------------------------+
'-REF-- (type-name2) ----------------------------------------------------'
default-values:
|--+- constant --------------------------------------------+-------|
+- datetime-special-register ---------------------------+
+- user-special-register -------------------------------+
+-CURRENT SCHEMA----------------------------------------+
+-NULL--------------------------------------------------+
'- cast-function --(--+- constant ------------------+-)-'
+- datetime-special-register -+
+- user-special-register -----+
'-CURRENT SCHEMA--------------'
identity-options:
|--+-----------------------------------------------------------+--|
| .-------------------------------------------------. |
| V .--------------------. | |
'-(-----------+-START WITH--+- numeric-constant -+---+-+--)-'
| .------------------. |
+-INCREMENT BY--+- numeric-constant ---+-+
| .-NO MINVALUE----------------. |
+-+-MINVALUE-- numeric-constant -+-----+
| .-NO MAXVALUE----------------. |
+-+-MAXVALUE-- numeric-constant -+-----+
| .-NO CYCLE-. |
+-+-CYCLE----+-------------------------+
| .-CACHE 20----------------. |
+-+-NO CACHE-----------------+---------+
| '-CACHE- integer-constant -' |
| .-NO ORDER-. |
'-+-ORDER----+-------------------------'
references-clause:
|--REFERENCES--+- table-name -+--+------------------------+-------->
'- nickname ---' | .-,------------. |
| V | |
'-(--- column-name -+--)-'
>--| rule-clause |--| constraint-attributes |-------------------|
rule-clause:
.-ON DELETE NO ACTION-----. .-ON UPDATE NO ACTION-.
|--*--+-------------------------+--*--+---------------------+--*--|
'-ON DELETE--+-RESTRICT-+-' '-ON UPDATE RESTRICT--'
+-CASCADE--+
'-SET NULL-'
default-clause:
.-WITH-.
|--+------+--DEFAULT--+--------------------+--------------------|
'-| default-values |-'
unique-constraint:
|--+------------------------------+--+-UNIQUE------+------------->
'-CONSTRAINT- constraint-name -' '-PRIMARY KEY-'
.-,-------------.
V |
>--(---- column-name -+--)----------------------------------------|
referential-constraint:
|--+------------------------------+--FOREIGN KEY----------------->
'-CONSTRAINT- constraint-name -'
.-,------------.
V |
>--(--- column-name -+--)--| references-clause |-----------------|
check-constraint:
|--+------------------------------+------------------------------>
'-CONSTRAINT- constraint-name -'
>--CHECK--(--| check-condition |--)----------------------------->
>--| constraint-attributes |------------------------------------|