次の方法で共有

第 2 章 : Microsoft Access 2000 クライアント/サーバー データベースの実装

  • [アーティクル]

Acey J. Bunch
Microsoft Corporation

February 2000

対象 : Microsoft® Access 2000

要約 : Microsoft Access 2000 プロジェクト ファイル (.adp) とツールのクライアント/サーバー ソリューションの開発に関する 3 部シリーズの 2 つ目として、ここでは、データベースのデザインの実行方法の概要を説明します。Microsoft Data Engine (MSDE) のデータベースとの接続の設定方法から始まり、データベースとそのテーブルの作成方法について説明します。State University のサンプル アプリケーションとその関連ファイルの使用方法も説明します。

ここをクリックして、State University のサンプル アプリケーションをダウンロードしてください。(英語）

内容

はじめに
State University のサンプル アプリケーション
新しいデータベースを作成する
テーブルを作成する
テーブル間にリレーションシップを作成する
テーブルにインデックスを作成する
テーブルに制約を作成する
トリガを作成する
データをインポートする
要約

はじめに

ここでは、3 部シリーズの 2 つ目 として、Microsoft Access 2000 のクライアント/サーバー ソリューションの開発方法について説明します。ここでは、データベース サーバーとの接続方法、新しいデータベースの作成方法、データベースのテーブルの構築と操作の方法について説明します。扱うすべての項目を State University のサンプル アプリケーションを使って説明します。

このシリーズのそのほかの記事では、Access クライアント/サーバー開発におけるユーザー インターフェイスとプログラミングについて詳しく説明します。

State University のサンプル アプリケーション

State University のサンプル アプリケーションは、Access 2000 プロジェクト ファイル (.adp) と Microsoft Data Engine (MSDE) の使用によって開発されました。このサンプル アプリケーションは、Access プロジェクトでのみ使われるさまざまな技術を使用するようデザインされていますが、通常、 Access データベースの開発に必要となる一般的な概念も説明します。Access プロジェクトでのデータベースのデザインの実装方法を説明する前に、State University のアプリケーションのデザインについて簡単に説明します。

シナリオ

あるソフトウェア開発者が、State University というコンピュータ研修専門の小さな会社からデータベースのアプリケーション開発を依頼されたと仮定します。この会社は Office 2000 にアップグレードしたところなので、Access 2000 でのデータベースのアプリケーション開発を依頼しており、これは、MSDE と Access のプロジェクト ファイルの新しい機能の使用に絶好の機会だと判断します。

このアプリケーションは、インストラクタ、学生、および提供される科目を追跡するために使用します。またこのアプリケーションは、管理スタッフの業務を支援するためにさまざまなレポートを作成します。このアプリケーションは、その利用と保持が簡単でなければなりませんが、同時にクライアント/サーバー データベース プラットフォームの機能をすべて備えていなければなりません。

データベース エンティティ

データベースデザインの最初の手順の 1 つは、リレーショナル データベースのテーブルを構成する主要なエンティティまたは情報の種類を確立することです。State University のデータベースのテーブルを構成する 4 つの主要エンティティは以下のとおりです。

  • インストラクタ -- 科目を教える人たち

  • 学生 -- 科目を履修する人たち

  • 科目 -- 提供される科目

  • 登録 -- どの学生が何の科目を履修しているか

最終的にはデータベースのテーブルになるエンティティを特定したのち、各テーブルに含まれるデータのすべての列 (フィールド) を一覧表示する必要があります。「Appendix 1: The State University Application Architecture (英語) 」では、"テーブル スキーマ" とも呼ばれる各テーブルの列、データ型、および記述について詳細に説明します。

アプリケーションのビジネス ルール

アプリケーションのデータ フローの制御と検証を行う特定のビジネス ルールは、前のセクションで特定されたエンティティのいくつかに関連します。次の表は、各エンティティに関連する主要ビジネス ルールの詳細を示しています。

表 2.1 ビジネス ルール

エンティティ ビジネス ルール
学生 データベースからある学生を削除する場合、その学生の登録の記録も削除する必要があります。
科目 科目は四半期ベースで提供されます。
登録 各四半期で、学生は 3 科目より多くは登録できません。
一般的なビジネス ルール 日付と時刻のフィールドをすべて検証する必要があります。 郵便番号 (米国) のフィールドはすべて、"郵便番号 + 4" の形式で検証する必要があります。たとえば、55555-5555 となります。

電話番号 (米国) のフィールドはすべて、地域番号を付けて検証する必要があります。たとえば、(555) 555-5555 となります。

この章では、データベースのデザインと実装についてのみ説明し、ユーザー インターフェイスやプログラミングについての説明はありません。ユーザー インターフェイスとプログラミングの構築については、「第 3 章 : Microsoft Access 2000 クライアント/サーバー アプリケーションの構築 」を参照してください。データベースのデザインの詳細については、 https://support.microsoft.com/download/support/mslfiles/sqlbol.exe でダウンロード可能な『SQL Server Books Online』の「データベースの作成と保守」を参照してください。

注意 : 『SQL Server Books Online』では MSDE のことはあまり説明されていませんが、『SQL Server Books Online』の資料は、SQL Server と同様に MSDE にも適用されます。

サンプル アプリケーションをインストールする

このシリーズの記事に沿うには 2 つの方法があります。ステップごとの手順に従ってサンプル アプリケーションを構築する方法と、完成しているサンプル アプリケーションをインストールして、それをリファレンスとして使用する方法です。サンプル アプリケーションを自分で構築する場合は、このセクションをスキップして、次のセクション「新しいデータベースを作成する 」に進んでください。サンプル アプリケーションをインストールする場合は、以下の手順に従ってください。

  1. Access 2000 をインストールします (Office 2000 のマニュアルを参照してください)。

  2. MSDE をインストールします (「 第 1 章 : Microsoft Access 2000 クライアント/サーバー開発の理解」で説明されています)。

  3. 次のセクションで説明されるとおりに、MSDE に State University のデータベースのサンプル ファイル (StateU.mdf) を関連付けて、StateU.adp 接続情報を構成します。

Access 2000 と MSDE の両方がインストールされていると想定します。インストールされていない場合は、「 第 1 章 : Microsoft Access 2000 クライアント/サーバー開発の理解」の「MSDE のインストール」を参照してください。

State University のデータベース ファイルをサーバーに関連付けて接続する

SQL Server または MSDE のデータベース ファイル (.mdf) をインストールする方法は 3 とおりあります。バックアップされたデータベースの復元を実行する方法、sp_attach_db システム ストアド プロシージャを使用してこのファイル ( .mdf) をデータベース サーバーに関連付ける方法、および Access の [データ リンク プロパティ] ダイアログ ボックスを使用する方法です。以下の実習では、[データ リンク プロパティ] ダイアログ ボックスを開いてサンプル データベースを関連付け、State University の Access プロジェクト ファイルを SQL Server または MSDE のデータベース サーバーに接続します。

  1. State University のサンプル アプリケーションの自己解凍ファイル (.exe) (この記事の最初でダウンロードが可能) をダウンロードした後、そのファイルに含まれるサンプル ファイルを任意のフォルダに zip 解凍します。

  2. SQL Server または MSDE をインストールしたコンピュータのデータ ディレクトリに StateU.mdf ファイルをコピーします。ローカル インストールの既定のデータ ディレクトリは「C:\MSSQL7\Data」です。

  3. Access 2000 で、サンプル ファイルと共にダウンロードされた StateU.adp Access プロジェクトを開きます。アプリケーションのメイン フォームが表示された場合は、[デザイン] ボタンをクリックしてメイン フォームを閉じます。

注意 : 接続情報がまだ構成されていないので、[データベース] ウィンドウに "stateU (未接続) : データベース" というタイトルが表示される場合があります。

  1. [ファイル] メニューの [接続] をクリックして、[データ リンク プロパティ] ダイアログ ボックスを開きます。

  2. 次のいずれかを実行します。

    • 使用しているコンピュータに SQL Server または MSDE がインストールされている場合は、サーバー名を「(ローカル)」または実際のコンピュータ名に変更します。

      または

    • SQL Server または MSDE がローカル コンピュータではなく、サーバーにインストールされている場合、サーバー名を SQL Server または MSDE がインストールされているサーバーの名前に変更します。

  3. 適切なユーザー名とパスワードを入力します。

    注意 :    SQL Server または MSDE の新しくインストールされたバージョンを使用し、セキュリティの設定を変更していない場合は、ユーザー名に「sa」と入力して、パスワードは入力しません。

  4. [データベース ファイルをデータベース名として添付する] オプションをクリックして、データベース名に**「StateU」と入力し、[ファイル名を使用する]** ボックスに「C:\MSSQL7\Data\StateU.mdf」と入力します。

  5. OK をクリックします。

このデータベース ファイルが関連付けられて Access プロジェクトがそのファイルに接続されると、State University のサンプル アプリケーション内のどのデータベース オブジェクトでも開くことができます。

新しいデータベースを作成する

この章およびほかの章では、以下のような小さな図が表示されます。

この図は、次に続くセクションで、State University のサンプル アプリケーションの特徴と機能を作成するために使用できるステップごとの手順が示されることを意味しています。

Access プロジェクト ファイルを作成する

新しい Access プロジェクト作成の最初の手順は、Access プロジェクト ファイル (.adp) の作成です。2 つある方法のいずれかでこのファイルを作成することができます。1 つの方法では、最初に Access を起動したときに表示される [Microsoft Access] スタートアップ ダイアログ ボックスの [データベース ウィザード、ページまたはプロジェクト] をクリックして (図 2.1 参照)、[OK] をクリックします。

図 2.1 [Microsoft Access] スタートアップ ダイアログ ボックス

別の方法として、既に開いている Access インスタンスで [ファイル] メニューの [新規作成] をクリックすることもできます。いずれの場合でも、図 2.2 に示されるように、[新規作成] ダイアログ ボックスが表示されます。[標準] タブの [プロジェクト (新しいデータベース)] アイコンをクリックして、[OK] をクリックします。

図 2.2 [データベースの新規作成] ダイアログ ボックス

次に、[データベースの新規作成] ダイアログ ボックスが表示されます。ここでは、.adp ファイルに名前を付けて、ハード ディスクまたはネットワーク ドライブの指定した場所に保存します。保存先のフォルダを指定して [ファイル名] ボックスにそのファイル名を入力し、[作成] をクリックします。 図 2.3 に示されるように、数分後に、Microsoft SQL Server データベース ウィザードの最初のページが表示されます。ここで、データベースに使用する SQL Server のコンピュータを選択して、ログイン ID とパスワードを入力し、サーバー データベースに名前を付けます。

図 2.3 に示されるように、数分後に、Microsoft SQL Server データベース ウィザードの最初のページが表示されます。ここで、データベースに使用する SQL Server のコンピュータを選択して、ログイン ID とパスワードを入力し、サーバー データベースに名前を付けます。

注意 :    Microsoft SQL Server データベース ウィザードは既定ではインストールされません。そのため、インストール用 CD-ROM を必要な場合に備えて用意しておく必要があります。

図 2.3 Microsoft SQL Server データベース ウィザード

State University のデータベースの作成

State University の新しい .adp ファイルとサーバー データベースを作成するには、以下の手順に従います。

  1. Microsoft Access を開いて、[Microsoft Access] スタートアップ ダイアログ ボックスの [データベース ウィザード、ページまたはプロジェクト] をクリックして、[OK] をクリックします。

  2. [新規作成] ダイアログ ボックスの [標準] タブの、[プロジェクト (新しいデータベース)] をクリックして、[OK] をクリックします。

  3. [データベースの新規作成] ダイアログ ボックスで、State University の .adp ファイルを保存するフォルダを選択し、ファイル名として**「StateU.adp」と入力し、[作成]** をクリックします。これによって、Microsoft SQL Server データベース ウィザードが起動します。

  4. Microsoft SQL Server データベース ウィザードの最初のページに以下の情報を入力します。

    • SQL Server の名前 : データベース サーバーの名前を入力します。お使いのローカル コンピュータで MSDE を実行している場合、そのコンピュータ名を入力するか (Microsoft WindowsR 95 または 98 や Microsoft Windows NTR Workstation 4.0 を使用している場合にコンピュータ名を確認するには、コントロール パネルの [ネットワーク] をクリックして、[識別] タブをクリックします。Microsoft Windows 2000 を使用している場合は、コントロール パネルの [システム] をクリックして、[ネットワーク ID] をクリックします)、または "(ローカル)" をデータベース サーバー名として使用します。別のコンピュータで MSDE または SQL Server を実行している場合は、そのコンピュータ名を入力する必要があります。

    • ログイン ID: 既定のアカウント名である「sa」を入力します。

    • パスワード: 既定値では空なので、前に sa アカウントにパスワードを割り当てていないかぎり、ここに何も入力しません。

    • SQL Server データベース名 : 新しいサーバー データベースの名前として「StateU」を入力します。

    この情報の入力が完了したら、[次へ] をクリックします。

  5. 次に表示されるページは確認のページなので、[完了] をクリックして、新しいサーバー データベースを作成します。

この時点で Access では、新しいサーバー データベース作成の過程を知らせるメッセージ ボックスが表示されます。作成が完了すると、[データベース] ウィンドウを含む標準の Access 環境が表示されます。

MSDE に接続する

Microsoft SQL Server データベース ウィザードの最初のページに入力された接続情報を用いて、ウィザードは、サーバーとの接続を確立します。接続情報は [データ リンク プロパティ] ダイアログ ボックス (図 2.4 参照) に保存されており、[ファイル] メニューの [接続] をクリックすると開くことができます。

図 2.4 [データ リンク プロパティ] ダイアログ ボックス

第 1 章で説明したように、[データ リンク プロパティ] ダイアログ ボックスを使って、Access プロジェクトの接続情報を変更または再定義することや、接続をテストすることができます。接続をテストする場合の例として、SQL Server にアプリケーションを展開する準備ができている場合が挙げられます。この場合は、MSDE を使ってそのアプリケーションを開発したのち、データベース ファイルを SQL Server データベースにコピーして、コピーしたデータベース ファイルを SQL Server に接続し、.adp ファイルにある接続情報を再構成して SQL Server データベースを指定します。[接続のテスト] をクリックして接続を検証し、接続が有効である場合、図 2.4 で示されるように確認のメッセージが表示されます。

サーバー データベースとの接続に使用するセキュリティ情報を再構成することもできます。MSDE のデータベースと SQL Server のデータベースに関しては、認証の形式が、Windows NT 統合と、MSDE/SQL Server 特有の 2 種類あります。Windows NT 統合認証では、Windows NT に確立されているとおりのユーザー名とパスワードが使用されます。MSDE/SQL Server 特有の認証では、データベースそのものに確立されているとおりのユーザー名とパスワードが使用されます。

注意   Windows 95 または Windows 98 オペレーティング システムで MSDE を実行している場合、Windows NT 統合認証はサポートされません。

データベース ファイルについて

上記の手順に従って State University のデータベースを作成したとき、実際には StateU.mdf と StateU.ldf. の 2 つ別個のファイルが作成されます。両方のファイルとも C:\MSSQL7\data フォルダ (MSDE を既定の場所にインストールしたと想定) に配置され、それぞれのファイルは既定のファイル サイズの約 2 MB に作成されています。

StateU.mdf ファイルには、アプリケーションが持っているすべてのサーバー データベース オブジェクトとデータ (この段階ではデータはまだ含まれていません) が含まれています。ファイル サイズの上限は 2 GB です。StateU.ldf ファイルは、システムの復旧が必要な場合に備えてすべてのトランザクションを記録するために MSDE で使用されるトランザクションのログ ファイルです。

テーブルを作成する

Access プロジェクトでテーブルを作成するプロセスは、Access データベースでテーブルを作成するプロセスと似ていますが、使用するツールはまったく異なります。.adp ファイルでは、MSDE データベースと SQL Server データベースのテーブルを作成して操作するために新しいセットのデータベース デザイン ツールを使用します。テーブル自体も .mdb ファイルのテーブルとは異なり、.adp ファイルでは、列のプロパティのペインがなく、すべての列のプロパティはテーブル デザイン グリッドに設定され、列を定義するときに選択するデータ型が .mdb ファイルより多くあります。

注意 :    .mdb データベースでは、テーブルに "レコード" と "フィールド" が含まれます。しかし、.adp データベースでは、レコードとフィールドを "行" と "列" ということがあります。この違いが生じるのは、Access プロジェクトが SQL Server の名前付け規則に従う傾向にあるからです。ここでは、"行" と "列" を使用します。

デザイン ビューでテーブルを開く

デザイン ビューで新しいテーブルまたは現在のテーブルを開くには、以下のような 3 とおりの方法があります。

  • [挿入] メニューの [テーブル] をクリックする。

  • [データベース] ウィンドウで、[オブジェクト] の [テーブル] をクリックし、[データベース] ウィンドウのツール バーにある [新規作成] または [デザイン] をクリックする。

  • データベース ダイアグラムで、以下の操作のいずれかを実行する。

    • [ダイアグラム] メニューの [新しいテーブル] をクリックする。

      または

    • ダイアグラムでマウスの右ボタンをクリックし、ショートカット メニューの [新しいテーブル] をクリックする。

注意 :    新しいテーブルを作成する場合、Access では、デザインを使った作業をする前にテーブルに名前を付けるようプロンプトが表示されます。

図 2.5 で示すように、デザイン ビューでテーブルを開く場合、テーブル スキーマがグリッド形式で表示されます。

図 2.5 テーブル デザイン グリッド

サーバー データベースのオブジェクトに名前を付ける

サーバー データベースに保存されている各オブジェクトは、"識別子の規則" という名前付け規則のセットに従う必要があります。これらの規則によって、データベースのオブジェクトが、文字、数値、記号を使って作られる方法が確立されます。テーブル、列、そのほかのデータベース サーバーのオブジェクトに名前を付ける場合、以下に注意してください。

  • 識別子は、文字、数値、記号などの 1 ～ 128 個の Unicode 文字の長さです。

  • 最初の文字は "a ～ z"、"A ～ Z"、"_"、"@"、または "#" である必要があります。

  • "@"、"$"、"#"、または "_" という記号を次に続く文字に使用できます。

  • 識別子は Transact-SQL 予約語であってはなりません。

  • スペースは埋め込みできません。

識別子がこれらの規則に従わない場合、その識別子を "区切り記号付き識別子" と呼びます。その識別子が参照されるときには常に、二重引用符 (" ") または角かっこ ([ ]) のいずれかの区切り記号を付けなければならないからです。たとえば、テーブル名に My Table のようにスペースを含める場合、そのテーブル名を Transact-SQL ステートメントで参照する場合は、"My Table" または [My Table] のように、区切り記号を付ける必要があります。

注意 :    識別子の最初の特定の記号には特別な意味があります。記号 "@" で始まる識別子は、ローカル変数またはローカル パラメータを表します。シャープ記号 "#" で始まる識別子は、一時テーブルまたは一時プロシージャを表します。二重シャープ記号 "##" で始まる識別子は一時グローバル オブジェクトを表します。Transact-SQL 関数には、二重の記号 "@@" で始まる名前のものもあります。これらの関数と混同してはいけないので、"@@" で始まる名前を使用しないでください。Transact-SQL と変数に関する詳細については、「 第 3 章 : Microsoft Access 2000 クライアント/サーバー アプリケーションの構築」を参照してください。

識別子の詳細については、『SQL Server Books Online』で "識別子" について検索してください。

列のプロパティを定義する

新しいテーブルを作成する場合、テーブルの各列を列のプロパティのセットによって定義する必要があります。また、各テーブルにつき、最高 1,024 列までを定義することができます。テーブルをサーバー データベースに保存する前に、そのテーブルの列名、データ型、およびサイズの各プロパティを定義する必要があります。追加する各列に対し 11 個のプロパティを指定することができます。次の表で各プロパティを説明します。

表 2.2 列のプロパティの定義

列のプロパティ 説明
列名 列の名前で、識別子の規則に従う必要があります。
データ型 列のデータ型。列のデータ型の詳細については、表 2.3 を参照してください。
長さ データ型の長さ。この値はデータ型に基づいて自動的に設定されますが、 binary、char、 nchar、 varbinary、 varchar、および nvarchar に変更することができます。
精度 decimal と numeric のデータ型のみに対して、使用する最大桁数を設定します。
桁数 decimal と numeric のデータ型のみに対して、使用する小数点以下の最大桁数を設定します。
Null を許容 このプロパティのチェック ボックスがオンの場合、列で Null 値を受け取ることができます。
既定値 テーブルに新しい行が挿入される際に、値が明示的に提供されない場合に使用される値です。
IDENTITY このプロパティは、整数ベースのデータ型にのみ有効です。このプロパティのチェック ボックスがオンになっている場合、列には新しい各行に対し自動的に増加するシステム割り当ての数値が含まれます。IDENTITY プロパティの機能は、Access データベース (.mdb) の オートナンバー型フィールドとよく似ていますが、自動的に増加する数値は新しい行が保存されるまでその行に追加も表示もされません。
IDENTITY シード IDENTITY 列に使用される最初の値です。
IDENTITY インクリメント 新しい行に対する増分です。
Is RowGuid このプロパティは、列がグローバルに一意な識別子 (GUID) の場合にチェック ボックスがオンになります。

列のプロパティの詳細については、[Microsoft Access ヘルプ] をクリックして Office アシスタントを表示するか [質問] タブを表示し、**「Column プロパティ」または「データベース ダイアグラム デザイナ」**と入力して [検索] をクリックするか、『SQL Server Books Online』で "列, プロパティ" について検索してください。

列名

各列に対して定義する列名は、テーブル内で一意で、識別子の規則に準拠していなければなりません。

データ型

Access プロジェクトで使用されるデータ型は、Access データベースで使用されるデータ型と似ていますが、いくつかの違いがあります。また、Access データベースよりも多くのデータ型が用意されています。使用可能なデータ型を次の表に示します。

表 2.3 データ型プロパティの定義

データ型 既定の長さ 説明
binary 10 n バイトの固定長のバイナリ データです。n は 1 ～ 8,000 の値である必要があります。保存領域のサイズは n + 4 バイトです。
bit 1 整数データ型で、1 または 0 です。
char 10 固定長の Unicode 以外の文字データで、最長 8,000 文字です。
datetime 8 1753 年 1 月 1 日から 9999 年 12 月 31 日までの日付と時刻で、精度は 300 分の 1 秒または 3.33 ミリ秒です。
decimal 9 -1038 - 1 ～ 1038 - 1 の、精度と小数点以下の桁数が固定された数値データです。
float 8 -1.79E + 308 ～ 1.79E + 308 の精度浮動小数点数の数値データです。
image 16 可変長のバイナリ データで、最長 231 - 1 (2,147,483,647) バイトです。
int 4 -231 (-2,147,483,648) ～ 231 - 1 (2,147,483,647) の整数値データです。
money 8 -263 (-922,337,203,685,477.5808) ～ 263 - 1 (+922,337,203,685,477.5807) の通貨データ値で、精度は通貨単位の 1 万分の 1 までです。
nchar 10 固定長の Unicode 形式のデータで、最長 4,000 文字です。
ntext 16 可変長の Unicode 形式のデータで、最長 230 - 1 (1,073,741,823) 文字です。
numeric 9 decimal のシノニムです。
nvarchar 50 可変長の Unicode 形式のデータで、最大 4,000 文字です。
real 4 -3.40E + 38 ～ 3.40E + 38 の精度浮動数値データです。
smalldatetime 4 1900 年 1 月 1 日 ～ 2079 年 6 月 6 日の日付と時刻で、精度は 1 分です。
smallint 2 215 (-32,768) ～ 215 - 1 (32,767) の整数値データです。
smallmoney 4 -214,748.3648 ～ 214,748.3647 の通貨データ値で、精度は通貨単位の 1 万分の 1 です。
text 16 可変長の Unicode 形式以外のデータで、最長 231 - 1 (2,147,483,647) 文字です。
timestamp 8 データベース全体で一意な数値です。
tinyint 1 0 ～ 255 の整数値データです。
uniqueidentifier 16 グローバルに一意な識別子 (GUID) です。
varbinary 50 n バイトの可変長のバイナリデータです。n の範囲は 1 ～ 8,000 です。保存領域のサイズは、入力データの実際のサイズに 4 バイトを加えたもので、 n バイトではありません。入力データのサイズ が 0 バイトの場合もあります。SQL-92 における "varbinary" のシノニムは "binary varying" です。
varchar 50 可変長の Unicode 形式以外のデータで、最長 8,000 文字です。

データ型の詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、**「データベース ダイアグラム デザイナ」**と入力して [検索] をクリックするか、『SQL Server Books Online』で "データ型" について検索してください。

ユーザー定義のデータ型

MSDE の機能の 1 つは、ユーザー独自のデータ型を定義できることです。ユーザー定義のデータ型は、SQL Server と MSDE のシステムのデータ型に基づいています。また、複数の列に同じ型のデータが含まれることを確保する場合に使用されます。システム提供型ストアド プロシージャである sp_addtype を使用して新しいデータ型を定義します。データ型を作成する場合、名前、物理型、また NULL 値を許容するかどうかを指定する必要があります。データ型を作成すると、そのデータ型は、テーブル デザイン グリッドのデータ型列プロパティ リストに表示されます。MSDE の "モデル" データベースにユーザー定義のデータ型を作成する場合、新しいデータ型は作成する新しいデータベースすべてで使用できることに注意してください。"モデル" データベースの詳細については、『SQL Server Books Online』で "モデル" について検索してください。

ユーザー定義のデータ型の使用方法を示すために、State University のデータベースに保存されている電話番号の形式を変更することになったと仮定します。これらの電話番号は 3 つの異なる列に保存されており、電話番号の形式を (555) 555-5555 に変更します。3 つの列それぞれに対しデータ型のプロパティを明示的に指定する代わりに、ユーザー定義のデータ型を各列で同じになるように作成することができます。ユーザー定義の phone データ型を作成する方法を以下の手順で示します。

ユーザー定義の phone データ型の作成

  1. [挿入] メニューの [ストアド プロシージャ] をクリックして、ストアド プロシージャ デザイナを開きます。

  2. CREATE PROCEDURE 句で、プロシージャの名前を CreatePhoneDataType に変更します。

  3. 既定のコメントを削除して、ストアド プロシージャの AS 句の次に以下の行を追加します。

    EXECUTE sp_addtype 'phone', 'varchar(14)', 'NOT NULL'

    図 2.6 CreatePhoneDataType ストアド プロシージャ

  4. ストアド プロシージャを閉じて保存します。

  5. [データベース] ウィンドウで [オブジェクト] の [ストアド プロシージャ] をクリックし、CreatePhoneDataType プロシージャを選んで [実行] をクリックします。

新しい phone データ型が存在するかどうかをテストするには、新しいテーブルを作成し、"データ型" 列のプロパティのセルをクリックします。下向きの矢印をクリックして使用可能なデータ型のリストを表示すると、リストの最後に phone データ型が表示されているはずです。

ストアド プロシージャの使用については、「 第 3 章 : Microsoft Access 2000 クライアント/サーバー アプリケーションの構築 」を参照してください。 sp_addtype システム ストアド プロシージャの詳細については、『SQL Server Books Online』で "sp_addtype" について検索してください。

Null 値を許容する

テーブルの各列に対し、Null 値を許容するかどうかを指定することができます。Null 値または NULL は、ゼロ (0) や空と同じではありません。NULL は存在しない値、または不明な値の代替文字です。通常、NULL があるということは、値が不明か定義されていないということです。Null 値が許容されない場合、データをテーブルに入力するユーザーは、列に 1 つの値を入力する必要があります。入力しないなら、新しい行はデータベースに挿入されません。 注意 : 主キーとして定義されている列のこのプロパティを変更することはできません。また、IDENTITY 列は NULL に設定できません。

注意 :    主キーとして定義されている列のこのプロパティを変更することはできません。また、IDENTITY 列は NULL に設定できません。

Null 値の詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、「データベース ダイアグラム デザイナ」と入力して [検索] をクリックするか、『SQL Server Books Online』で "Null 値" について検索してください。

既定値

ユーザーがテーブルに新しい行を入力する場合、列に配置する必要のある値をユーザーが入力しないために、ある値を指定しておくことが好ましい場合がよくあります。これらの値は "既定値" と呼ばれ、テーブル デザイン グリッドを使用してテーブル レベルでそれらの値を定義することができます。既定値が数値の場合は、既定の列のプロパティ セルにその数値を入力するだけです。既定値が文字列の場合は、その文字列を引用符 (') で囲む必要があります。

既定値の詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、「データベース ダイアグラム デザイナ」と入力して [検索] をクリックするか、『SQL Server Books Online』で "既定値の定義" について検索してください。

IDENTITY データ型

列を IDENTITY 列として定義する場合、その列には、テーブル内の各行を一意に識別するシステム割り当ての連続値が含まれており、これは Access .mdb データベースのオートナンバー データ型によく似ています。IDENTITY 列を含むテーブルに値を挿入する場合、最後に使用された IDENTITY 値 (IDENTITY シード列プロパティ) と、列の作成時に指定されたインクリメント値 (IDENTITY インクリメント列プロパティ) に基づいて、次の識別子は MSDE により自動的に生成されます。

IDENTITY 列プロパティを設定できるのは、decimal、 int、 numeric、 smallint または tinyint データ型の列だけです。Null 値は許容されません。また、1 つのテーブルには 1 つの IDENTITY 列しか使用できません。「第 3 章 : Microsoft Access 2000 クライアント/サーバー アプリケーションの構築」では、IDENTITY 列が定義されている特定のテーブルで、最後に挿入された行の IDENTITY 列の値を取得する方法について説明します。

IDENTITY 値の詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、「データベース ダイアグラム デザイナ」と入力して [検索] をクリックするか、『SQL Server Books Online』で "IDENTITY 列" について検索してください。

主キーを設定する

リレーショナル データベースにテーブルを構築する場合、主キーを定義して、テーブル内の行を一意に識別する必要があります。データベースのテーブルに主キーを定義する場合、そのテーブルをほかのテーブルと関連付けることができるので、不要なデータに消費される容量を減らすことができます。1 つのテーブルには 1 つの主キーしか設定できませんが、1 つの主キーを複数の列で構成することができます。

テーブルをデザイン ビューで開いているときに主キーをテーブルに設定するには、以下の 2 つの方法があります。

  • [テーブル デザイン] ツール バーの [主キー] ボタンを使用する。テーブルで 1 つの列に主キーを設定するには、デザイン ビューでテーブルを開いて、列名の隣りの矢印をクリックして主キーとして定義する列を選択し、[テーブル デザイン] ツール バーの [主キー] ボタンをクリックします。Access によって主キーが作成され、それに "PK_<テーブル名>" と名前が付けられます。また、一意制約のある列が Access によって自動的に確立されます。

    複数列の主キーを作成する場合は、Ctrl キーを押しながら主キーに含めるすべてのフィールドを選択し、[テーブル デザイン] ツール バーの [主キー] ボタンをクリックします。制約の詳細については、この章の「テーブルで制約を作成する」を参照してください。

  • テーブルの [プロパティ] ダイアログ ボックスを使用する。[プロパティ] ダイアログ ボックスにたどり着くにはさまざまな方法がありますが、最も簡単な方法では、デザイン ビューでテーブルを開いて、テーブル デザイン グリッドの [プロパティ] ボタンをクリックします。[プロパティ] ダイアログ ボックスが表示されたら、 図 2.11で示すように、[インデックス/キー] タブをクリックします。

    主キーを設定するには、[インデックス/キー] タブの [新規作成] をクリックして、主キーとして定義する列を選択します。"PK_<テーブル名>" などのようにインデックスに名前を付けて、それに一意な制約を作成します。制約の詳細については、この章の「テーブルで制約を作成する」を参照してください。

このように、主キーを作成するには、[テーブル デザイン] ツール バーの [主キー] ボタンを使う方が簡単です。ただし、作成する主キーに独自の名前付け規則を使用する場合は、[プロパティ] ダイアログ ボックスを使う必要があります。

主キーの詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、「主キーを定義する」と入力して [検索] をクリックするか、『SQL Server Books Online』で "主キー制約" について検索してください。

SQL を使ってテーブルを作成する

テーブルの構築にテーブル デザイナ グリッドを使用することのほかに、SQL CREATE TABLE ステートメントを使用することもできます。このステートメントは、データ定義言語 (DDL) ステートメントです。Access では、まずストアド プロシージャを作成し、CREATE TABLE ステートメントを入力した上で実行します。CREATE TABLE ステートメントについて詳しく説明することはこの記事の範囲を超えていますが、その使用の詳細については、『SQL Server Books Online』で "CREATE TABLE" について検索してください。

インストラクタのテーブル スキーマ

Access プロジェクトでテーブルを構築する基本を説明してきましたが、次に State University のデータベースにテーブルを作成してみます。Instructors テーブルの "スキーマ" ("テーブルの定義") を以下に示します。

表 2.4 Instructors テーブルのスキーマ

列 データ型 制約 説明
InstructorID Int (identity(1,1)) 主キー制約 各インストラクタの ID
FirstName varchar(25) Null 不可 名
MiddleName varchar(25) Null 許容 ミドル ネームまたはイニシャル
LastName varchar(25) Null 不可 姓
Address varchar(255) Null 不可 番地
City varchar(50) Null 不可 市区町村
State varchar(2) Null 不可 州都道府県
Zip varchar(10) Null 不可 郵便番号
HomePhone phone (varchar) Null 不可 自宅の電話番号
Email varchar(100) Null 許容 電子メール アドレス

このテーブルに定義された既定値はありませんが、HomePhone 列のデータ型プロパティを、前のセクションで説明したユーザー定義のデータ型の phone に設定することに注意してください。State University のテーブルはすべて、「 Appendix 1: The State University Application Architecture. (英語) 」で参照することができます。

インストラクタのテーブルを作成する

Instructors という State University テーブルを作成するには、以下の手順に従ってください。

  1. [挿入] メニューの [テーブル] をクリックします。メッセージが表示されたら、テーブルに「Instructors」と名前を付けます。次に、テーブル デザイン グリッドが表示されます。

  2. 表 2.4 に示すテーブル スキーマを使用して Instructors テーブルのすべての列を定義します。

  3. InstructorID 列をクリックして、[テーブル デザイン] ツール バーの [主キー] ボタンをクリックし、テーブルの主キーを設定します。

  4. テーブルの [プロパティ] ダイアログ ボックスを開き、[インデックス/キー] タブで PK_Instructors という主キーがあることを確認します。

  5. Instructors テーブルを閉じて保存します。

操作が完了すると、作成したテーブルのデザインは図 2.7 と同じになっているはずです。

図 2.7 デザイン ビューにおける Instructors テーブル

これで Access プロジェクトでテーブルを作成する方法は終了です。「 Appendix 1 」で示すデータベース スキーマを使って State University のほかのすべてのテーブルを作成してください。次のセクションでは、Access プロジェクトでテーブル間にリレーションシップを確立する方法を説明します。

テーブル間にリレーションシップを作成する

リレーショナル データベース システムでは、通常、不要なデータを防ぐことと、別のテーブルのデータに関連しているテーブル データの削除を防ぐことが重要な目標です。これらの目標は、"参照整合性" の使用によって達成することができ、参照整合性を確立する一環として、テーブル間のリレーションシップを定義します。

Access プロジェクトでテーブル間のリレーションシップを定義する方法は主に 2 つあります。データベース ダイアグラムを使用する方法と、SQL を使ってリレーションシップを定義する方法です。テーブル レベルでテーブル リレーションシップを定義する場合、"宣言参照整合性" (DRI) と呼ばれます。

テーブル リレーションシップの定義の詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、「テーブル リレーションシップ」と入力して [検索] をクリックするか、『SQL Server Books Online』で "標準化" について検索してください。

データベース ダイアグラムを使用する

データベース ダイアグラムは Access .mdb データベースの [リレーションシップ] ウィンドウによく似ていますが、より多くの機能があります。実際、リレーションシップを定義するだけでなく、データベース ダイアグラム自体にテーブルを作成することもできます。データベース ダイアグラム ウィンドウで、データベースにテーブルとリレーションシップがグラフィック表示されます。ここで実行できる操作のいくつかを以下に示します。

  • [テーブルの表示] リストからテーブルをドラッグして、データベース ダイアグラムに追加する。

  • ダイアグラムまたはデータベースからテーブルを削除する。

  • 選択したテーブル、またはデータベース ダイアグラム全体を保存する。

  • テーブル間のリレーションシップを作成、編集、または削除する。

  • テーブル、およびテーブルの列の定義を、作成、編集、または削除する。

  • プロパティ ページを開いて、ダイアグラムに示されるオブジェクトを編集する。または、ダイアグラムにグラフィック表示されないインデックスや制約などのデータベース オブジェクトを作成、編集、または削除する。

  • テキスト ラベルを作成、編集、または削除する。

  • リレーションシップ名を表示または非表示にする。

  • 表示される列のプロパティを変更する。

テーブル リレーションシップの定義に関する詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、「データベース ダイアグラム」と入力して [検索] をクリックします。

データベース ダイアグラムを作成する

データベース ダイアグラムを作成するには、[挿入] メニューの [ダイアグラム] をクリックし、[表示] メニューの [テーブルの表示] をクリックします。図 2.8 に示すように [テーブルの表示] リストが表示されます。このリストでダイアグラムに含めるテーブルをクリックして、データベース ダイアグラム ウィンドウにドラッグします。ただし、一度に 1 つのテーブルしかドラッグできません。

図 2.8 [テーブルの表示] リスト

データベース ダイアグラム ウィンドウにテーブルがあると、1 つのテーブルで 1 つの列をクリックして、別のテーブルの関連付ける列に選択した列をドラッグ アンド ドロップすることによって、テーブル間にリレーションシップを定義することができます。もちろん関連付ける列は同様のデータ型である必要があります。また一般に、主キーと外部キーを組み合わせて使って、一対一または一対多のテーブル リレーションシップを定義します。

関連付ける列を一方の列にドロップすると、図 2.9 に示すように、[リレーションを作成] ダイアログ ボックスが表示されます。

図 2.9 [リレーションを作成] ダイアログ ボックス

表 2.5 で [リレーションを作成] ダイアログ ボックスのさまざまな要素を説明します。

表 2.5 [リレーションを作成] ダイアログ ボックスの要素

要素 説明
リレーション名 システム割り当てのリレーションシップ名を表示します。リレーションシップ名を変更するには、このボックスに新しい名前を入力します。
主キー テーブル リレーションシップで主キー テーブルの名前を表示し、その後で主キーを構成する列を表示します。外部キー テーブルの下に表示される列に合致する別の列を選択することができます。
外部キー テーブル リレーションシップで外部キー テーブルの名前を表示し、その後で外部キーを構成する列を表示します。主キー テーブルの下に表示される列に合致する別の列を選択することができます。
作成時に既存データを確認する リレーションシップが作成されるときに、外部キー テーブルの既存のデータに外部キー制約を適用します。このボックスが選択されると、外部キー制約に違反するデータについて知らせるエラー メッセージが表示されます。
INSERT と UPDATE に対するリレーションを有効にする INSERT ステートメントまたは UPDATE ステートメントを使用してデータが外部キー テーブルで追加または更新される場合に、外部キー制約を適用します。
レプリケーションに対するリレーションを有効にする 外部キー テーブルが別のデータベースにコピーされるときはいつでも外部キー制約をコピーします。

[リレーションを作成] ダイアログ ボックスの [OK] をクリックすると、データベース ダイアグラム ウィンドウにリレーションシップがグラフィック表示されます。

データベース ダイアグラムを使ったテーブル リレーションシップの定義に関する詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、「テーブル間にリレーションシップを作成する」と入力して [検索] をクリックします。

学生と登録のリレーションシップを作成する

これまでに State University のテーブルはすべて作成していると思いますが、この実習では、Students テーブルと Enrollments テーブルの間に一対一のリレーションシップを確立します。

  1. [挿入] メニューの [ダイアグラム] をクリックして、データベース ダイアグラム ウィンドウを開きます。

  2. [ダイアグラム デザイン] ツール バーの [テーブルの表示] ボタンをクリックします。[テーブルの表示] ボックスが表示されます。

  3. Students テーブルをクリックして データベース ダイアグラム ウィンドウへドラッグします。Enrollments テーブルに対しても同じ操作を行います。

  4. Students テーブルの StudentID 列をクリックして、Enrollments テーブルの StudentID 列へドラッグします。[リレーションを作成] ダイアログ ボックスが表示されます。

  5. [OK] をクリックすると、図 2.10で示すように、リレーションシップがグラフィック表示されます。

    図 2.10 FK_Enrollment_Students リレーションシップ

  6. データベース ダイアグラムを閉じて、**「StateU_Diagram」**という名前を付けて保存します。

これで Access プロジェクトでテーブル リレーションシップを作成する方法がわかりました。先に進んで、「 Appendix 1」で示すデータベース スキーマを使って State University のほかのすべてのテーブル リレーションシップを作成します。次に、Access プロジェクトでインデックスを作成する方法を説明します。

テーブルにインデックスを作成する

State University のテーブルを構築したとき、各テーブルに対して主キーも作成しました。主キーを確立することによって、主キーの列に基づいて各テーブルに対して主キーのインデックスが自動的に構築されます。本でインデックス (索引) を使うことによってより迅速に特定のセクションを開くことができるのと同様に、これらのインデックスによって、テーブルに含まれる情報により迅速にアクセスできるようになります。ただし、ほかの列を情報の検索に頻繁に使用する場合があり、これらの列にもインデックスを構築すると便利です。

SQL Server と MSDE のデータベース サーバーには、"クラスタ化" インデックスと "非クラスタ化" インデックスの 2 種類があります。クラスタ化インデックスでは、テーブルの行の物理順がキー値の論理順と同じですが、1 つのテーブルに 1 つのクラスタ化インデックスしか含めることができません。インデックスが非クラスタ化の場合、テーブルの物理順はキー値の論理順に合致しません。通常、クラスタ化インデックスによって、非クラスタ化インデックスより迅速にデータにアクセスすることができます。

各テーブルにつき、最高 249 個までの非クラスタ化インデックスを含めることができます。また、1 つのインデックスに 1 ～ 16 個までの列を含めることができますが、1 つのインデックスは 900 バイトの幅を超えてはいけません。

"一意インデックス" と呼ばれる種類のインデックスを作成することもできます。一意インデックスでは、同じインデックス値を 2 つの行に含めることはできません。主キーのインデックスは自動的に一意インデックスとして作成されます。

注意 :    通常は、インデックスを作成した列のデータが頻繁に検索される場合にだけ、インデックスをテーブルに作成する必要があります。インデックスによってディスク空き領域が消費され、行の追加、削除、更新の操作が遅くなります。ほとんどの場合、データ取得の速度におけるインデックスの利点は、これらの欠点に大きく勝ります。ただし、ご使用のアプリケーションでデータの更新がかなり頻繁に行われる場合や、ディスク空き領域に制約がある場合は、インデックスの数を制限します。

Access プロジェクト データベース テーブルにインデックスを作成するには、図 2.11 に示すように、テーブルの [プロパティ] ダイアログ ボックスの [インデックス/キー] タブを使用します。テーブルの [プロパティ] ダイアログ ボックスは、デザイン ビューからでも、データベース ダイアグラム ウィンドウからでも開くことができます。

図 2.11 [プロパティ] ダイアログ ボックスの [インデックス/キー] タブ

表 2.6 で、[プロパティ] ダイアログ ボックスの [インデックス/キー] タブのさまざまな要素について説明します。

表 2.6 [インデックス/キー] タブの要素

要素 説明
選択したインデックス 選択したテーブルの最初のインデックスの名前を表示します。プロパティを表示するために別のインデックスを選択するには、[選択したインデックス] リストを開きます。
型 選択したテーブルのインデックスまたはキーのオブジェクトの種類が、インデックス、主キー、または一意キーのいずれであるかを表示します。
新規作成 このボタンをクリックして、選択したデータベース テーブルに新しいインデックスを作成します。
削除 このボタンをクリックして、テーブルから選択したインデックスを削除します。
列名 インデックスの列のリスト、主キー、または一意制約を表示します。このリストでは、列名の追加、変更、削除を行うことができます。
インデックス名 選択したインデックスの名前を表示します。このボックスに新しい名前を入力することによってインデックス名を変更することができます。
インデックス ファイル グループ 選択したインデックスを保存するファイル グループの名前を選択します。ファイル グループとは、データベース内でファイルをグループ化するための管理メカニズムです。この設定を有効にするには、ユーザー定義のファイル グループが少なくとも 1 つは必要です。ほとんどの場合、既定の PRIMARY ファイル グループのままでかまいません。ファイル グループの詳細については、『SQL Server Books Online』で "ファイル グループ" について検索してください。
UNIQUE の作成 このオプションを選択して、選択したデータベース テーブルに対して一意制約または一意インデックスを作成します。[制約] または [インデックス] のいずれかをクリックして、制約とインデックスのいずれを作成するのかを指定します。一意インデックスを作成する場合、[重複するキーを無視] オプションを設定して、インデックスが作成された列のそれぞれの値が一意であることを確実にできます。
FILL FACTOR 各インデックス ページの許容量を指定する FILL FACTOR を表示します。このオプションは、データベースのパフォーマンスを微調整するために使用しますが、ほとんどの場合、既定値の 0 (ゼロ) のままにしておく必要があります。
パッド インデックス FILL FACTOR に 0 (ゼロ) パーセントより大きい値を指定し、一意インデックスを作成するというオプションを選択した場合、FILL FACTOR に指定したのと同じパーセント値を各内部ノードに空けておくように SQL Server または MSDE に指示することができます。既定値で、SQL Server または MSDE では、インデックス サイズは 2 行に設定されます。
CLUSTERED として作成 このオプションを選択して、選択したデータベース テーブルにクラスタ化インデックスを作成します。
動的に統計値を再計算しない このオプションを選択して、SQL Server または MSDE に、前に作成した統計を使用するよう指定します。この選択は、最適の結果が生じない場合があり、お勧めしません。

IX_Students_HomePhone インデックスを作成する

この実習では、Students テーブルの HomePhone 列に非クラスタ化インデックスを作成します。顧客が、学生の ID や家の電話番号で学生の検索ができることを望んでいると仮定します。StudentID 列が Students テーブルの主キーなので、StudentID 列には既に主キーのインデックスがあります。ここでは、HomePhone 列にインデックスを作成します。

  1. [データベース] ウィンドウで、[オブジェクト] の [テーブル] をクリックして Students テーブルを選択し、[デザイン] をクリックします。

  2. [表示] メニューの [プロパティ] をクリックして、テーブルの [プロパティ] ダイアログ ボックスを開きます。

  3. [インデックス/キー] タブをクリックします。

  4. [新規作成] をクリックして新しいインデックスを作成します。

  5. [列名] リストで下向きの矢印をクリックして、列に HomePhone を選択します。

  6. [インデックス名] ボックスに、インデックス名として「IX_Students_HomePhone」と入力します。[プロパティ] ダイアログ ボックスが上記の図 2.11 と同じようになったはずです。

  7. テーブルの [プロパティ] ダイアログ ボックスを閉じて、保存メッセージが表示されたらテーブル デザイン グリッドを閉じて [OK] をクリックします。

これで、Access プロジェクトでインデックスを作成する方法がわかりました。先に進んで、「 Appendix 1」で示すデータベース スキーマを使って、State University のほかのすべてのテーブル インデックスを作成します。次に、Access プロジェクトで制約を設定する方法を説明します。

インデックスの詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、**「テーブルのインデックス」**と入力して [検索] をクリックするか、"『SQL Server Books Online』"で "インデックス, 説明" について検索してください。

テーブルに制約を作成する

制約は、特定の列または列の集合に入力する値を制限 (制約) する方法です。制約は、データの整合性と一貫性の両方を実行する役割を果たすので、リレーショナル データベースに参照整合性を確立する場合に非常に重要です。

SQL Server と MSDE のデータベースには、5 種類の主な制約があり、既にそのうちの 4 つを使って作業を行いました。表 2.7 で、5 種類の制約とそれらの説明の概要を示します。

表 2.7 制約の種類

制約 説明
既定の制約 既定の制約によって、ユーザーが値の入力に失敗した場合に列に必ず入力される値を定義することができます。Access データベース (.mdb) とは異なり、MSDE と SQL Server の既定の制約は、レコードが最初に追加されるときではなく、レコードが保存されるときに入力されます。
UNIQUE 制約 UNIQUE 制約によって、テーブルの主キー以外の指定された列に、重複した値が入力されないことが確保できます。
主キー制約 主キー制約によって、主キーの列に重複した値も Null 値も入力されないことが確保されます。主キー制約を使って、参照整合性と一意であることが達成できます。
外部キー制約 外部キー制約は、主キー制約または一意制約と共に機能して、指定されたテーブル間に参照整合性を設定します。
CHECK 制約 CHECK 制約によって、テーブルの 1 つ以上の列に有効なデータ値またはデータ形式が指定されます。

既にこれらの制約のうちの 4 つを使って、以下のように作業を行いました。

  • State University のテーブルを構築し、特定の列に既定値を定義したときに、既定の制約を使いました。

  • State University のテーブルに主キーを確立したとき、一意インデックスと主インデックスに UNIQUE 制約と主キー制約の両方を定義しました。

  • State University のデータベースで、データベース ダイアグラムを使ってテーブル間にリレーションシップを確立したときに、外部キー制約も設定しました。

以上のように、まだ使っていない制約は CHECK 制約だけです。CHECK 制約は、値をテーブルの特定の列に保存する前にその値を確認するために使用されます。CHECK 制約を使うことは、Access の .mdb データベースにおいてデータ評価規則を確立することに相当します。図 2.12 で示すように、CHECK 制約は、テーブルの [プロパティ] ダイアログ ボックスの [テーブル] タブを使って、テーブル レベルで定義します。

図 2.12 [プロパティ] ダイアログ ボックスの [テーブル] タブ

表 2.8 で、[プロパティ] ダイアログ ボックスの [テーブル] タブのさまざまな要素について説明します。

表 2.8 [テーブル] タブの要素

要素 説明
選択したテーブル 選択したテーブルの名前を表示します。
テーブル名 選択したテーブルの名前を表示します。テーブルの名前を変更するには、このボックスに新しい名前を入力します。
テーブル ファイル グループ テーブル データが保存されるファイル グループの名前を表示します。既定のファイル グループは PRIMARY ファイル グループです。
テキスト ファイル グループ テーブルのテキストとイメージが保存されるファイル グループの名前を表示します。
選択した制約 選択したテーブルに関連付けられた CHECK 制約のリストを表示します。
新規作成 選択したテーブルに新しい CHECK 制約を作成します。
削除 選択した CHECK 制約をデータベースから削除します。
制約式 選択した CHECK 制約の SQL ステートメントを表示します。新しい制約に対しては、このボックスを終了する前に SQL ステートメントを入力する必要があります。
制約名 選択した制約の名前を表示します。このボックスに新しい名前を入力することによって、制約の名前を変更することができます。
作成時に既存データを確認する 制約が作成される前にテーブルに存在したすべてのデータが制約に対して検証されることを確保します。
INSERT と UPDATE に対する制約を有効にする データがテーブルで挿入または更新されるときに制約を適用します。
レプリケーションに対する制約を有効にする テーブルが別のデータベースにコピーされるときに制約を適用します。

CHECK 制約を書き込む際には、以下の点に注意してください。

  • CHECK 制約は、真か偽かを評価する式であり、式によって真と評価される場合にのみ、指定した列または列の集合に値が受け入れられます。

  • 1 つの列 (列レベル) に複数の CHECK 制約を適用することができますが、CHECK 制約は、ソース列しか参照できません。また、制約は、作成された順序で確認されます。

  • 1 つの CHECK 制約を複数の列 (テーブル レベル) に適用することができますが、CHECK 制約はソース テーブルの列しか参照できません。

  • CHECK 制約を新しいデータと既にテーブルに存在するデータの両方に適用することもできますが、新しいデータのみに適用することも可能です。既定では、両方に CHECK 制約が適用されます。

  • 複数の式は、AND と OR の論理演算子を使って結合することができます。

  • CHECK 制約では、サブクエリは許容されません。

CHECK 制約は、単一の戻り値 (真か偽) で評価を行う SQL 式です。式が偽を返した場合、データは列に受け取られず、INSERT 操作も UPDATE 操作も失敗します (CHECK 制約がこれらの操作に有効な場合)。式が真を返した場合、データは列に受け入れられ、次の CHECK 制約 (ある場合) が処理されます。

SQL 式には、識別子、値、演算子を組み合わせて使うことができます。たとえば、列の値が常に 100 未満であることをチェックするには、以下の式を使用します。

  ColumnName < 100

列の値が値のリストの範囲内であることをチェックするには、以下のように、IN 演算子を使用します。

  ColumnName IN ('Value1','Value2','Value3')

列の値が数値だけであることをチェックするには、以下のように、LIKE 演算子を使用します。

ColumnName LIKE '[0-9][0-9][0-9]'

CHECK 制約の詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、「CHECK 制約」と入力して [検索] をクリックするか、『SQL Server Books Online』で "CHECK 制約" について検索してください。

CK_Instructors_Zip CHECK 制約を作成する

この実習では、State University のサンプル データベースの Instructors テーブルに CHECK 制約を作成します。

  1. [データベース] ウィンドウで [オブジェクト] の [テーブル] をクリックし、Instructors テーブルを選択して [デザイン] をクリックします。

  2. [表示] メニューの [プロパティ] をクリックして、テーブルの [プロパティ] ダイアログ ボックスを開きます。

  3. [テーブル] タブの [新規作成] をクリックして、新しい CHECK 制約を作成します。

  4. [制約式] ボックスに以下の式を入力します。

    Zip LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

  5. [制約名] ボックスに「CK_Instructors_Zip」と入力します。

  6. [作成時に既存データを確認する] チェック ボックスと [INSERT と UPDATE に対する制約を有効にする] チェック ボックスがオンになっていることを確認します。

  7. [プロパティ] ダイアログ ボックスを閉じます。

  8. Instructors テーブル デザイン グリッドを閉じて、変更を保存します。

制約をテストするには、Instructors テーブルを開いて、テーブルのレコードの 1 つで、Zip 列の値を**「555」**に変更して、別の行に移動してみます。CHECK 制約によりステートメントの競合が存在するというメッセージが表示されます。この行は保存されていないことに注意してください。

これで、Access プロジェクトで CHECK 制約を作成する方法がわかりました。先に進んで、「 Appendix 1」で示すデータベース スキーマを使って State University のほかのすべての CHECK 制約を作成します。

制約の詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、「制約」と入力して [検索] をクリックするか、『SQL Server Books Online』で "制約, 概要" について検索してください。

トリガを作成する

前のセクションでテーブル リレーションシップを定義したとき、Access データベース (.mdb) の作成時に使用可能な連鎖更新と連鎖削除の機能が Access プロジェクト ファイル (.adp) の作成時には使用できないことに気付いたでしょうか。Access では、ユーザー インターフェイスによって、Access データベースでテーブル リレーションシップを構築するときにこの参照整合性の機能を定義することができますが、Access プロジェクトでは、この機能のプログラムをユーザー自身が作成する必要があります。Access プロジェクトのこの特徴は Access データベースに比べてより難しく思えるかもしれませんが、実際にはサーバー レベルでのデータ保持においてより高度な管理ができることがすぐにわかります。

連鎖更新と連鎖削除の機能を SQL Server または MSDE データベースで確立するには、"トリガ" というデータベース オブジェクトを使います。トリガは、UPDATE 操作、INSERT 操作、または DELETE 操作がテーブルに対して実行されたときに自動的に起動するストアド プロシージャの特別な形です。

注意 :    ストアド プロシージャについては、「 第 3 章 : Microsoft Access 2000 クライアント/サーバー アプリケーションの構築」の「ストアド プロシージャを作成する」で詳しく説明します。

重要 : 宣言参照整合性 (DRI) と、連鎖更新および連鎖削除のトリガとを同時に使用することはできません。相互に排他的なものだからです。特定のテーブルに連鎖更新と連鎖削除のトリガを作成する場合は、連鎖されるテーブルを指定する外部キー リレーションシップを削除する必要があります。たとえば、下記の例で説明する概要のとおりに連鎖削除のトリガを使用するには、リレーションシップの [プロパティ] ダイアログ ボックスの [INSERT と UPDATE に対するリレーションを有効にする] チェック ボックスをオフにする必要があります。DRI と、連鎖更新および連鎖削除の機能とを両方使用する場合は、主キーを含む行が削除される前に、関連するすべてのレコードを削除するストアド プロシージャを使用する必要があります。そして、主テーブルから行を削除するときはいつも、直接テーブルから行を削除するのではなく、ストアド プロシージャを呼び出してから実行します。

参照整合性の保持のほかに、以下の場合にトリガを使用することができます。

  • 複雑なデータ整合性規則を保持する

  • 現在の合計と算出列を常に更新する

  • ビジネス ルールを強制的に適用する

  • ほかのストアド プロシージャを呼び出す

トリガは、Visual Basic® コードでの Before イベントの実行と同じように考えることができます。つまり、Before イベントは、要求された作業アイテムが実行される "前に" 実行されます。トリガの場合は、データベースのデータに施されるある種の変更が実行される前にトリガが実行されます。データベースで行が挿入、更新、または削除されるときはいつもトリガを定義して、テーブルに変更が加えられる前に動作を実行することができます。

トリガを作成するには、[データベース] ウィンドウで、[オブジェクト] の [テーブル] をクリックして、トリガを定義するテーブルをマウスの右ボタンでクリックします。図 2.13 に示すように、ショートカット メニューの [トリガ] をクリックして、[テーブルのトリガ <テーブル名>] ダイアログ ボックスを開きます。

図 2.13 [テーブルのトリガ <テーブル名>] ダイアログ ボックス

このダイアログ ボックスで、トリガを作成、編集、または削除することができます。トリガを作成または編集する場合、Access では [トリガ] デザイン ウィンドウが開き、ここにトリガが起動するときに実行される Transact-SQL コードを入力することができます。

注意 :    Transact-SQL については、「 第 3 章 : Microsoft Access 2000 クライアント/サーバー アプリケーションの構築」の「ストアド プロシージャを作成する」で説明します。

トリガを効果的に使用するには、"挿入された" テーブルと "削除された" テーブルというトリガによって使用される 2 つの特別なテーブルに相互に関連付ける必要があります。これらの一時テーブルは、トリガ テーブル (主テーブル) でトリガに影響される行のコピーを保持するためにトリガによって使用されます。行を挿入する場合、挿入された行は挿入されたテーブルとトリガ テーブルの両方に配置されます。行を削除する場合、削除された行は、トリガ テーブルから削除されて削除されたテーブルに配置されます。最後に、行を更新する場合は、トリガ テーブルでトリガに影響される行が削除されたテーブルにコピーされ、要求された変更が挿入されたテーブルにコピーされます。

トリガはこれらの特別なテーブルを使って、変更がデータベースに適用されて変更できなくなる前にデータの変更のスナップショットを作成します。これらのスナップショットを使うことにより、ROLLBACK TRANSACTION ステートメントを発行して、変更を無視することができます。

トリガを作成するには、以下の構文を使用します。

CREATE TRIGGER trigger_name
ON table_name
FOR {INSERT or UPDATE or DELETE}
AS
SQL statements

注意 :    [テーブルのトリガ <テーブル名>] ダイアログ ボックスを使ってトリガを作成するとき、CREATE TRIGGER ステートメントの大部分が自動的に作成されます。

trgStudent_Delete_Enrollment トリガを作成する

この実習では、State University のサンプル データベースで、Students テーブルにトリガを作成します。

  1. [データベース] ウィンドウで、[オブジェクト] の [テーブル] をクリックし、Students テーブルをマウスの右ボタンでクリックして、ショートカット メニューの [トリガ] をクリックします。

  2. [テーブルのトリガ <テーブル名>] ダイアログ ボックスの [新規作成] をクリックして、[トリガ] デザイン ウィンドウを開きます。

  3. 以下のコードを [トリガ] デザイン ウィンドウに入力します。

    CREATE TRIGGER trgStudent_Delete_Enrollment
ON Students
FOR DELETE
AS
    --関連する登録レコードがある場合は削除する。
    IF EXISTS (SELECT StudentID FROM Enrollment
        WHERE StudentID = (SELECT StudentID FROM deleted))
        BEGIN
        DELETE FROM Enrollment
            WHERE StudentID = (SELECT StudentID FROM deleted)
        END
    RETURN

  4. [トリガ] デザイン ウィンドウを閉じて、トリガに**「trgStudent_Delete_Enrollment」** という名前を付けて保存します。

トリガをテストするには、FK_Enrollment_Students リレーションシップの [プロパティ] ダイアログ ボックスの [INSERT と UPDATE に対するリレーションを有効にする] チェック ボックスがオフになっていることを確認します。このチェック ボックスをオフにした後で、Students テーブルを開いて、登録の行に関連する学生の行を削除し、Enrollments テーブルを確認して、関連する行が削除されていることを確認します。

注意 :    一度に複数行を削除しようとすると、トリガによってエラー メッセージが表示され、最初の行しか削除されません。一度に複数行を削除する必要のある場合は、代わりにストアド プロシージャを使用する必要があります。

trgEnrollment_Insert_Student トリガを作成する

トリガを使って、ビジネス ルールを設定することもできます。たとえばここでは、アプリケーションのビジネス ルールの 1 つが、学生は各四半期に 3 科目より多く登録できないということです。トリガを使って、四半期で学生が既に最高の 3 科目に達している場合に新しい登録レコードの挿入を防ぐコードを作成することができます。

  1. [データベース] ウィンドウで、[オブジェクト] の [テーブル] をクリックして、Enrollments テーブルをマウスの右ボタンでクリックし、ショートカット メニューの [トリガ] をクリックします。

  2. [テーブルのトリガ <テーブル名>] ダイアログ ボックスの [新規作成] をクリックして、[トリガ] デザイン ウィンドウを開きます。

  3. 以下のコードを [トリガ] デザイン ウィンドウに入力します。

    CREATE TRIGGER trgEnrollment_Insert_Student
ON Enrollment
FOR INSERT
AS

    --学生が最高の 3 科目に達しているかどうかチェックする。
    DECLARE @StudentID int
    DECLARE @CourseID int
    DECLARE @CourseCount int
    DECLARE @Quarter varchar(10)
    DECLARE @Year varchar(4)

    SELECT @StudentID = StudentID, @CourseID = CourseID
        FROM inserted

    SELECT @Quarter = Quarter, @Year = Year
        FROM Courses
        WHERE CourseID = @CourseID

    SELECT @CourseCount = Count(*)
        FROM Enrollment AS A INNER JOIN Courses AS B
        ON A.CourseID = B.CourseID
        WHERE A.StudentID = @StudentID
        AND  B.Quarter = @Quarter
        AND B.Year = @Year

    IF @CourseCount > 3
        BEGIN
        ROLLBACK TRANSACTION
        RAISERROR 50001 'この学生の登録数が最高の 3 科目に達している。'
        END

    RETURN



    

  1. [トリガ] デザイン ウィンドウを閉じて、トリガに「trgEnrollment_Insert_Student」という名前を付けて保存します。
    2. 



トリガをテストするために、学生フォームを開いて、1 人の学生に対し同じ年の同じ四半期に 4 科目を登録してみましょう。


トリガの詳細については、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、**「トリガ」**と入力して [検索] をクリックするか、『SQL Server Books Online』で "トリガ, 概要" について検索してください。


データをインポートする


多くの場合、テーブルを作成するために使うデータは、Excel ワークシートやテキスト ファイルなど外部ソースからのデータと、既存の Access の .mdb データベースからのデータです。Access には独自のインポート ツールがありますが、MSDE がインストールされている場合、"データ変換サービス ウィザード" というより強力なインポート ツールを使用することができます。以下のセクションでは、これらの各ツールを使用して、State University データベースにデータをインポートする方法を説明します。


Access インポート ウィザードを使ってデータをインポートする


Access 環境内からデータをインポートする場合、図 2.14 で示すように、[インポート] ダイアログ ボックスを使って作業します。Access で使うインポート ウィザードの種類は、インポートするファイルの種類によって決まります。インポートするファイルの種類は、[ファイルの種類] ボックスで指定することができます。




図 2.14 [インポート] ダイアログ ボックス


注意 :    Access データベース (.mdb ファイル) からデータをインポートする場合、Access ではインポートされるテーブルに基づいて新しいテーブルが作成されます。既存のテーブルにデータを追加することはできません。mdb ファイルから既存のテーブルにデータを追加するには、MSDE に組み込まれているデータ変換サービス ウィザードを使う必要があります。




Instructors データを Excel ワークシートからインポートする


この実習では、Access のインポート機能を使って、Excel ワークシートからインストラクタのリストをインポートします。データは State University のサンプル データベースの既存の Instructors テーブルに追加されます。


    

  1. State University Access プロジェクト ファイル (StateU.adp) を開きます。
    

    2. 

  2. [ファイル] メニューの [外部データの取り込み] をポイントして、[インポート] をクリックします。[インポート] ダイアログ ボックスが表示されます。
    

    3. 

  3. [ファイルの種類] ボックスで、[Microsoft Excel (*.xls)] をクリックします。
    

    4. 

  4. State University のサンプル ファイルをインストールしたフォルダを見つけて、Instructors.xls ファイルをクリックして、[インポート] をクリックします。
    

    5. 

  5. ワークシート インポート ウィザードの先頭ページで、[先頭行をフィールド名として使う] チェック ボックスをオンにして、[次へ] をクリックします。
    

    6. 

  6. ワークシート インポート ウィザードの 2 ページ目で、[次のテーブルに保存する] をクリックし、ドロップダウン リスト ボックスで Instructors テーブルをクリックして、[次へ] をクリックします。
    

    7. 

  7. ワークシート インポート ウィザードの次のページで、Instructors がテーブルとして一覧表示されているのを確認して、[完了] をクリックします。
    

    8. 



データがインポートされたことを確認するには、Instructors テーブルを開きます。10 個の追加インストラクタ レコードがあります。Instructors データをインポートした後で、上記と同じ手順に従って Courses.xls ファイルのデータを Courses テーブルにインポートします。


Access のインポート ツールを使ったデータのインポートについては、[Microsoft Access ヘルプ] の [質問] タブまたは Office アシスタントで、「データのインポート」と入力して [検索] をクリックします。


データ変換サービス ウィザードを使ってデータをインポートする


データをインポートするより強力な方法が、MSDE と共にインストールされるデータ変換サービス (DTS) ウィザードです。DTS ウィザードによって、OLE DB ベースのアーキテクチャを使って、複数の異なるソース間でのデータのインポートとエクスポートができます。このウィザードによって、MSDE と、OLE DB、ODBC、またはテキストファイル形式との間で、データのインポート、エクスポート、変換を実行できます。データの転送元、転送先、そして、データがインポートされるときに変換が必要かどうかを指定することができます。


変換 とは、ソース データが転送先で保存される前にそのデータに適用される一連の操作です。たとえば、DTS ウィザードによって、1 つ以上の変換元列から新しい値を計算することや、1 つの列を複数値に分けて別個の変換先列に保存することができます。変換によって、インポートとエクスポートのときの、データの検証、操作、強化が簡単になります。




Students データを Access データベースからインポートする


この実習では、DTS ウィザードを使って、Access の .mdb データベースから Students データをインポートします。Students データは、State University のサンプル データベースの既存の Students テーブルに追加されます。


    

  1. Windows の [スタート] ボタンをクリックして、[プログラム]、[MSDE] をポイントし、[データのインポートとエクスポート] をクリックします。データ変換サービス ウィザードが開きます。
    

    2. 

  2. [次へ] をクリックして、[データ ソースの選択] ページを開きます。
    

    3. 

  3. [変換元] ボックスの [Microsoft Access] をクリックします。
    

    4. 

  4. [ファイル名] ボックスの右の参照ボタン ([...]) をクリックして、State University のサンプル ファイルをインストールしたフォルダを参照します。Students.mdb ファイルをクリックして、[次へ] をクリックします。
    

    5. 

  5. [変換先の選択] ページで、[変換先] ボックスの [Microsoft OLE DB Provider for SQL Server] をクリックして、[サーバー] ボックスがサーバー名に設定されていることを確認します。次に、ログオン情報を入力して [データベース] ボックスの [StateU] をクリックし、[次へ] をクリックします。
    

    注意 :    使用可能なすべてのデータベースを一覧表示するには、[最新] ボタンをクリックしてください。
    

    6. 

  6. 次のページで、[変換元データベースからテーブルをコピー] をクリックして、[次へ] をクリックします。
    

    7. 

  7. [変換元テーブルの選択] ページで、変換元が "Students" で、変換先が "[StateU].[dbo].[Students]" になっていることを確認して、[次へ] をクリックします。
    

    8. 

  8. [パッケージの保存、スケジュール設定、およびレプリケート] ページで、[すぐに実行] チェック ボックスがオンになっていることを確認して、[次へ] をクリックします。
    

    9. 

  9. DTS ウィザードの最後のページで、[完了] をクリックします。
    

    10. 



データがインポートされたことを確認するために Students テーブルを開きます。30 個の学生レコードが追加されているはずです。


DTS ウィザードの詳細については、『SQL Server Books Online』で "データ変換サービス, 概要" について検索してください。


要約


この章では、Access と、SQL Server または MSDE を使ったデータベースのデザインの実行について説明します。「 第 3 章 : Microsoft Access 2000 クライアント/サーバー アプリケーションの構築」 で、形式とレポートの構築、プログラミング、Access プロジェクトの管理と展開について説明します。SQL Server または MSDEF を使ったデータベースのデザインと実行の詳細については、『SQL Server Books Online』の「データベースの作成と管理」と、「 Appendix 2: Additional Resources for Working with Microsoft Access 2000 Projects (英語) 」のリソース リストを参照してください。


--------------------------------------------


特に記載していない場合、ここに記載されている会社、製品、人物、キャラクタ、およびデータなどは架空のものです。実在する個人名、会社名、イベント名などとは一切関係ありません。