制約の確認をサンプルのテーブルを作成して行う
- スキーマを考える
- 制約リスト
- 上記図の太字属性を主キーとする
- すべての項目について NULLは許容しない
- テーブル「得意先マスタ」の 属性 「得意先コード」 の桁数は 数字２桁とする ex- 「05」、「16」
- テーブル「商品マスタ」の 属性 「商品コード」 の桁数は 数字２桁とする ex- 「09」、「26」
- テーブル「商品マスタ」の 属性「定価」 は 属性「仕入値」以上とする
- テーブル「受注」の「納品先コード」はテーブル「得意先マスタ」に存在する得意先とする
- テーブル「受注明細」 はテーブル「受注」に依存する
- テーブル「受注明細」 の属性「商品コード」 テーブル「商品マスタ」に存在する商品とする
- DDLの作成
- create_table.sql
- drop_table.sql
- テーブルの作成
- テストデータの作成
- 商品マスタ
- 顧客マスタ
- 受注
- 受注明細
- create_view.sql 上記の表を結合した View
- 制約の確認
- 「商品コード 」が'01' のレコードの削除を試みる
- 参照されていないレコードを削除
- 「受注No」が'1'の「受注」 レコードの削除
- 「商品コード」'AA'での登録を試みる
以下のような関係および、整合性を保つための制約を考える。
|
「受注」のレコードが削除されたら、そのレコードを親とする「受注明細」を削除する制約を設定しようとしたが、
なぜかエラーが発生し設定できない･･･ SQL Server 7.0 Books Onlineを確認すると、 「CASCADE DELETE 参照オプションは SQL Server では使用できません。このオプションは、 親の行を削除するときに親と子の両方の値を削除する場合に使用します。SQL Serverでは、 テーブルのトリガを使用してこのオプションを有効にする必要があります」 とのこと。 MSDNライブラリ 2001年1月 のレファレンスをみる限り SQL Server2000 では対応しているのたが、 (最初はそっちをみてたのでなぜできないのかずっと悩んでいた・・・ |
上記の制約を 以下のように SQL ファイルにまとめた
-- ************************************ -- 得意先マスタ -- ------------------------------------ -- 制約 各項目の not null -- 得意先コード 数字 2桁(0埋め) -- ************************************ create table customer_master ( customer_code char(2) not null, customer_name varchar(30) not null, constraint pk_customer_master primary key(customer_code), check (customer_code like '[0-9][0-9]') ) -- ************************************ -- 商品マスタ -- ------------------------------------ -- 制約 各項目の not null -- 商品コード 数字 2桁(0埋め) -- ************************************ create table article_master ( article_code char(2) not null, article_name varchar(30) not null, price smallmoney not null, cost smallmoney not null, constraint pk_article_master primary key(article_code), check (article_code like '[0-9][0-9]'), check (price >= cost) ) -- ************************************ -- 受注 -- ------------------------------------ -- 制約 各項目の not null -- 得意先コードの参照制約、 -- および 外部キー制約 -- ************************************ create table receive_order ( order_no int not null, customer_code char(2) not null, order_date char(10) not null, constraint pk_receive_order primary key(order_no), foreign key (customer_code) references customer_master(customer_code) ) -- ************************************ -- 受注明細 -- ------------------------------------ -- 制約 各項目の not null -- 商品コードの参照制約 -- および 外部キー制約 -- ************************************ create table order_item ( order_no int not null, seqno int not null, article_code char(2) not null, qty int not null, price smallmoney not null, constraint pk_order_item primary key(order_no,seqno), foreign key (order_no) references receive_order(order_no), foreign key (article_code) references article_master(article_code) )
テーブルの削除
作成した SQL ファイルを SQL Server から実行
(サンプルのデータも読み込む)
c:\temp フォルダに置くものとする
C:\MSSQL7\Binn>isql -S cafeaulait -U sa -P -d lesson -i c:\temp\create_table.sql 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 2 3> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> 42> 4 3> 44> 45> 46> 47> 48> 49> 50> 51> 52> 53> 54> 55> 56> 57> 58> 59> 60> 61> 62> 6 3> 64> 65> 66> 67> 68> 69> 70> 71> 72> 73> 74> 75> 76> C:\MSSQL7\Binn>isql -S cafeaulait -U sa -P -d lesson -i c:\temp\insert_data.sql 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 2 3> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> 42> 4 3> 44> 45> 46> 47> 48> 49> 50> 51> 52> 53> 54> 55> 56> 57> 58> 59> 60> 61> 62> 6 3> 64> 65> 66> 67> 68> 69> 70> 71> 72> 73> 74> 75> 76> 77> 78> 79> 80> 81> 82> 8 3> 84> 85> 86> (1 row affected) (1 row affected) (1 row affected) (1 row affected) (1 row affected) (1 row affected) (1 row affected)
insert_data.sql サンプルデータを作成する
| article_code | article_name | price | cost |
|---|---|---|---|
| 01 | ボールペン | 100.00 | 80.00 |
| 02 | 消しゴム | 80.00 | 56.00 |
| 03 | ポストイット | 50.00 | 30.00 |
| customer_code | customer_name |
|---|---|
| 01 | 株式会社 BELL |
| 02 | SOMY 株式会社 |
| order_no | customer_code | order_date |
|---|---|---|
| 1 | 01 | 20020122 |
| 2 | 02 | 20020129 |
| order_no | seqno | article_code | qty | price |
|---|---|---|---|---|
| 1 | 1 | 01 | 10 | 110.00 |
| 1 | 2 | 02 | 60 | 90.00 |
| 2 | 1 | 02 | 3 | 30.00 |
受注伝票
| 受注番号 | 受注日 | 顧客コード | 顧客名 | 連番 | 商品コード | 商品名 | 数量 | 単価 | 合計金額 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 20020122 | 01 | 株式会社 BELL | 1 | 01 | ボールペン | 10 | 100.00 | 1000.00 |
| 1 | 20020122 | 01 | 株式会社 BELL | 2 | 02 | 消しゴム | 60 | 80.00 | 4800.00 |
| 2 | 20020129 | 02 | SOMY 株式会社 | 1 | 02 | 消しゴム | 3 | 80.00 | 240.00 |
C:\MSSQL7\Binn>isql -S cafeaulait -U sa -P -d lesson 1> delete from article_master where article_code = '01' 2> go Msg 547, Level 16, State 1, Server CAFEAULAIT, Line 1 DELETE ステートメントは COLUMN REFERENCE で、制約 'FK__order_ite__artic__1BC821D D' と矛盾しています。矛盾が発生したのは、データベース 'lesson'、テーブル 'order_ite m', column 'article_code' です。 ステートメントは終了されました。 0 rows affected) 1>
制約が作成してあるため、参照されているマスタデータは削除できない。
CREATE TABLE 時に制約名を指定していないので、システムが制約名をつけている。
1> delete from article_master where article_code = '03' 2> go (1 row affected)
参照されていないレコードは問題なく削除できる
1> delete from receive_order where order_no = 1 2> go Msg 547, Level 16, State 1, Server CAFEAULAIT, Line 1 DELETE ステートメントは COLUMN REFERENCE で、制約 'FK__order_ite__order__1AD3FDA 4' と矛盾しています。矛盾が発生したのは、データベース 'lesson'、テーブル 'order_ite m', column 'order_no' です。 ステートメントは終了されました。 (0 rows affected)
商品マスタと同じく、受注明細から参照されているので削除することはできない
1> insert into article_master( 2> article_code, article_name, price, cost 3> )values( 4> 'AA', 'ポストイット', 50, 30 5>) 6> go Msg 547, Level 16, State 0, Server CAFEAULAIT, Line 1 INSERT ステートメントは COLUMN CHECK で、制約 'CK__article_m__artic__14270015' と矛盾しています。矛盾が発生したのは、データベース 'lesson'、テーブル 'article_m aster', column 'article_code' です。 ステートメントは終了されました。 (0 rows affected)