| MSDE FunClub |
|
最終更新日 : 2000/07/22 |
|
Microsoft Data Engine FunClub
|
Since 2000.07.22
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第２章１７６ｐ 〜 １８４ｐ掲載】 |
-- [DDL系SQL文の基礎（例題８）EX3-07.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- AccessのNorthwindデータベースの社員テーブルを除く、残り
-- すべてのテーブルを作成するものです
--
-- Access95/97 テキスト型のサイズはバイト数です
-- Access2000のテキスト型のサイズはUnicodeでの文字数です
-- Access2000の場合は、nchar型/nvarchar型を使ってください
-- もしくは文字数を２倍にしてバイト数に換算して、char型/varchar型
-- を使ってください
-- ここでは 単純に、Access95/97 でのテーブル移行を考えています
--
-- これから操作する既定のデータベースを MySampleTest にします
USE MySampleTest
GO
-- ( 件処理されました)のメッセージの表示を抑止します
SET NOCOUNT ON
GO
--同名のストアドプロシージャを削除
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'PROC_表削除' AND type = 'P ' )
BEGIN
DROP PROCEDURE PROC_表削除
PRINT 'ストアドプロシージャ [PROC_表削除]を削除しました'
END
GO
-- テーブルの存在を調べ、存在したらそれを削除する
-- ストアドプロシージャの登録
CREATE PROCEDURE PROC_表削除
@tblname varchar(30) -- 削除する表の名前
AS
DECLARE @sqlstr varchar(80) -- SQL文の文字列
IF EXISTS (SELECT name FROM sysobjects
WHERE name = @tblname AND type = 'U ' )
BEGIN
SELECT @sqlstr = 'DROP TABLE ' + @tblname
EXECUTE( @sqlstr )
PRINT @sqlstr
END
GO
-- 表を削除する
-- 削除する順番は、参照整合性の多側テーブル（外部キー側）から削除する
EXECUTE PROC_表削除 '受注明細'
EXECUTE PROC_表削除 '商品'
EXECUTE PROC_表削除 '商品区分'
EXECUTE PROC_表削除 '仕入先'
EXECUTE PROC_表削除 '受注'
EXECUTE PROC_表削除 '運送会社'
EXECUTE PROC_表削除 '得意先'
EXECUTE PROC_表削除 '都道府県'
-- EXECUTE PROC_表削除 '社員' EX03-06 の例題です（ここでは削除しない）
GO
-- 表を作成する
-- 作成する順番は削除と逆。参照整合性の主キー側から。
-- *****************
-- 【得意先】
-- *****************
CREATE TABLE 得意先
(
--主キー（オートナンバー型）
得意先コード int IDENTITY(1,1) NOT NULL CONSTRAINT PK_得意先 Primary key ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
フリガナ varchar(40) CHECK( フリガナ <> '' ) ,
--値要求[はい] 空文字列の許可[いいえ] インデックス[重複あり]
得意先名 varchar(40) NOT NULL CHECK(得意先名 <> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
担当者名 varchar(30) CHECK(担当者名 <> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
部署 varchar(30) CHECK( 部署<> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[重複あり]
郵便番号 varchar(10) CHECK( 郵便番号<> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
トドウフケン varchar(30) CHECK( トドウフケン<> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[重複あり]
都道府県 varchar(15) CHECK( 都道府県<> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[重複あり]
住所1 varchar(60) CHECK( 住所1<> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
住所2 varchar(60) CHECK( 住所2<> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[重複あり]
電話番号 varchar(24) CHECK( 電話番号<> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
ファクシミリ varchar(24) CHECK( ファクシミリ<> '')
)
-- ［重複あり］インデックスの作成
CREATE INDEX IDX_得意先名 ON 得意先( 得意先名 )
CREATE INDEX IDX_郵便番号 ON 得意先( 郵便番号 )
CREATE INDEX IDX_都道府県 ON 得意先( 都道府県 )
CREATE INDEX IDX_住所1 ON 得意先( 住所1 )
CREATE INDEX IDX_電話番号 ON 得意先( 電話番号 )
GO
-- *****************
-- 【運送会社】
-- *****************
CREATE TABLE 運送会社
(
--主キー（オートナンバー型）
運送コード int IDENTITY(1,1) NOT NULL CONSTRAINT PK_運送会社 Primary key ,
--値要求[はい] 空文字列の許可[いいえ] インデックス[いいえ]
運送会社 varchar(40) NOT NULL CHECK( 運送会社<> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[重複あり]
電話番号 varchar(24) CHECK( 電話番号 <> '')
)
-- ［重複あり］インデックスの作成
CREATE INDEX IDX_電話番号 ON 運送会社( 電話番号 )
GO
-- *****************
-- 【受注】
-- *****************
CREATE TABLE 受注
(
--主キー
受注コード int NOT NULL CONSTRAINT PK_受注 Primary key ,
--値要求[いいえ] インデックス[重複あり] 既定値[=0]
得意先コード int DEFAULT 0 ,
--値要求[いいえ] インデックス[重複あり]
社員コード int ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
出荷先名 varchar(40) CHECK( 出荷先名 <> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[重複あり]
出荷先郵便番号 varchar(10) CHECK( 出荷先郵便番号 <> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
出荷先都道府県 varchar(20) CHECK( 出荷先都道府県 <> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
出荷先住所1 varchar(60) CHECK( 出荷先住所1 <> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
出荷先住所2 varchar(60) CHECK( 出荷先住所2 <> ''),
--値要求[いいえ] インデックス[いいえ]
運送区分 int ,
--値要求[いいえ] インデックス[重複あり]
受注日 datetime ,
--値要求[いいえ] インデックス[いいえ]
締切日 datetime ,
--値要求[いいえ] インデックス[重複あり]
出荷日 datetime ,
--値要求[いいえ] インデックス[いいえ] 既定値[=0]
運送料 money DEFAULT 0 ,
-- *****************
-- 参照整合性の定義
-- *****************
FOREIGN KEY(得意先コード) REFERENCES 得意先 (得意先コード) ,
FOREIGN KEY(社員コード) REFERENCES 社員 (社員コード) ,
FOREIGN KEY(運送区分) REFERENCES 運送会社(運送コード)
)
-- ［重複あり］インデックスの作成
CREATE INDEX IDX_得意先コード ON 受注( 得意先コード )
CREATE INDEX IDX_社員コード ON 受注( 社員コード )
CREATE INDEX IDX_出荷先郵便番号 ON 受注( 出荷先郵便番号 )
CREATE INDEX IDX_受注日 ON 受注( 受注日 )
CREATE INDEX IDX_出荷日 ON 受注( 出荷日 )
GO
-- *****************
-- 【仕入先】
-- *****************
CREATE TABLE 仕入先
(
--主キー 既定値[=0]
--注意：Accessの設計では既定値の設定があるが、主キーに既定値があるのは変です
仕入先コード int NOT NULL CONSTRAINT PK_仕入先 Primary key ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
フリガナ varchar(80) CHECK( フリガナ <> ''),
--値要求[はい] 空文字列の許可[いいえ] インデックス[重複あり]
仕入先名 varchar(40) NOT NULL CHECK( 仕入先名 <> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
担当者名 varchar(30) CHECK( 担当者名 <> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
部署 varchar(30) CHECK( 部署 <> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[重複あり]
郵便番号 varchar(10) CHECK( 郵便番号 <> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
トドウフケン varchar(30) CHECK( トドウフケン <> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
都道府県 varchar(15) CHECK( 都道府県<> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
住所1 varchar(60) CHECK( 住所1<> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
住所2 varchar(60) CHECK( 住所2<> '') ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[重複あり]
電話番号 varchar(24) CHECK( 電話番号<> ''),
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
ファクシミリ varchar(24) CHECK( ファクシミリ<> ''),
--値要求[いいえ] 空文字列の許可[いいえ](Accessのハイパーリンク型はありません)
--残念ながら TEXT型にCHECK制約は作れません
ホームページ text
)
-- ［重複あり］インデックスの作成
CREATE INDEX IDX_仕入先名 ON 仕入先( 仕入先名 )
CREATE INDEX IDX_郵便番号 ON 仕入先( 郵便番号 )
CREATE INDEX IDX_電話番号 ON 仕入先( 電話番号 )
GO
-- *****************
-- 【商品区分】
-- *****************
CREATE TABLE 商品区分
(
--主キー（オートナンバー型）
区分コード int IDENTITY(1,1) NOT NULL CONSTRAINT PK_商品区分 Primary key ,
--値要求[はい] 空文字列の許可[いいえ] インデックス[重複なし]
区分名 varchar(30) NOT NULL CHECK( 区分名<> '') UNIQUE ,
--値要求[いいえ] 空文字列の許可[いいえ]
--残念ながら TEXT型にCHECK制約は作れません
説明 text ,
--値要求[いいえ]
図 image
)
GO
-- *****************
-- 【商品】
-- *****************
CREATE TABLE 商品
(
--主キー 既定値[=0]
--注意：Accessの設計では既定値の設定があるが、主キーに既定値があるのは変です
商品コード int NOT NULL CONSTRAINT PK_商品 Primary key ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
フリガナ varchar(80) CHECK( フリガナ<> ''),
--値要求[はい] 空文字列の許可[いいえ] インデックス[重複あり]
商品名 varchar(40) NOT NULL ,
--値要求[いいえ] インデックス[重複あり]
仕入先コード int ,
--値要求[いいえ] インデックス[重複あり]
区分コード int ,
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
梱包単位 varchar(20) CHECK( 梱包単位<> ''),
--値要求[いいえ] インデックス[いいえ] 既定値[=0]
単価 money DEFAULT 0 CHECK( 単価 >= 0 ),
--値要求[いいえ] インデックス[いいえ] 既定値[=0]
在庫 smallint DEFAULT 0 CHECK( 在庫 >= 0 ),
--値要求[いいえ] インデックス[いいえ] 既定値[=0]
発注済 smallint DEFAULT 0 CHECK( 発注済 >= 0 ),
--値要求[いいえ] インデックス[いいえ] 既定値[=0]
発注点 smallint DEFAULT 0 CHECK( 発注点 >= 0 ),
--値要求[いいえ] インデックス[いいえ] 既定値[=0]
生産中止 bit DEFAULT 0 ,
-- *****************
-- 参照整合性の定義
-- *****************
FOREIGN KEY(仕入先コード) REFERENCES 仕入先 (仕入先コード) ,
FOREIGN KEY( 区分コード ) REFERENCES 商品区分( 区分コード )
)
-- ［重複あり］インデックスの作成
CREATE INDEX IDX_商品名 ON 商品( 商品名 )
CREATE INDEX IDX_仕入先コード ON 商品( 仕入先コード )
CREATE INDEX IDX_区分コード ON 商品( 区分コード )
GO
-- *****************
-- 【受注明細】
-- *****************
CREATE TABLE 受注明細
(
--値要求[いいえ->はいに変更しました] インデックス[重複あり]
受注コード int NOT NULL,
--値要求[はい] インデックス[重複あり]
商品コード int NOT NULL ,
--値要求[はい] インデックス[いいえ] 既定値[=0]
単価 money NOT NULL DEFAULT 0 ,
--値要求[はい] インデックス[いいえ] 既定値[=1]
数量 smallint NOT NULL DEFAULT 1 CHECK( 数量 > 0 ) ,
--値要求[はい] インデックス[いいえ] 既定値[=0]
割引 real NOT NULL DEFAULT 0 CHECK( 割引 BETWEEN 0 AND 1 ) ,
--連結主キーの設定
CONSTRAINT PK_受注明細 PRIMARY KEY ( 受注コード, 商品コード),
-- *****************
-- 参照整合性の定義
-- *****************
FOREIGN KEY(受注コード) REFERENCES 受注 (受注コード),
FOREIGN KEY(商品コード) REFERENCES 商品 (商品コード)
)
-- ［重複あり］インデックスの作成
CREATE INDEX IDX_受注コード ON 受注明細( 受注コード )
CREATE INDEX IDX_商品コード ON 受注明細( 商品コード )
GO
-- *****************
-- 【都道府県】
-- *****************
CREATE TABLE 都道府県
(
--値要求[いいえ] 空文字列の許可[いいえ] インデックス[いいえ]
トドウフケン varchar(30) CHECK( トドウフケン<> ''),
--主キーの設定
都道府県 varchar(15) NOT NULL CONSTRAINT PK_都道府県 Primary key
)
GO
