Sql Sql Server T Sql

SQL Serverにおける外部キーの理解

リレーショナルデータベースを設計する際、データの整合性を維持するためにテーブル間に関係を設定することが重要です。この目的を達成する方法の一つが外部キーの使用です。SQL Serverにおける外部キーは、一つのテーブルにおいて他のテーブルの行を一意に識別するフィールド（またはフィールドのコレクション）です。他のデータベースシステム（例えばPostgreSQL）から移行してきた場合、SQL Serverにおける構文と実装の違いに混乱することがあるかもしれません。

問題: 外部キーの作成

与えられたシナリオでは、exams、question_bank、answer_bankの3つのテーブルを作成しようとしています。目標は、question_bank内にexamsテーブルを参照する外部キーを作成することですが、SQLコードを実行するとエラーが発生します。エラーメッセージの主要なポイントは以下の通りです。

Msg 8139, Level 16, State 0, Line 9 外部キーの参照カラム数が参照されるカラム数と異なります。テーブル ‘question_bank’。

これは、外部キーの定義に不一致があることを示しています。

解決策: 外部キーを正しく定義する

ステップ1: テーブル定義を確認する

question_bankテーブル内の外部キーを正しく設定する方法を詳しく見ていきましょう。

現在のテーブル設定

元のSQLはquestion_bankテーブルを以下のように作成しています。

create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null,
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint question_exam_id foreign key references exams(exam_id)
);

外部キーはexamsテーブルのexam_idを参照することを意図していますが、適切な構文と構造が欠けています。

ステップ2: 外部キーの定義を更新する

外部キーの関係を正しく定義するために、外部キーの列（question_exam_id）が参照されるテーブルの主キー（exam_id）とどのように関連しているかを指定します。

create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null,
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);

主な変更点:

外部キー名を指定する: 外部キー制約に名前を付けること（例: fk_questionbank_exams）は明確さのために良い習慣です。
外部キー列を正しく参照する: 外部キーが参照されるテーブル内の正しい列を参照することを確認します。

ステップ3: 変更を実装する

次に、既存のquestion_bankテーブルを削除し（存在する場合）、修正された外部キー定義で再作成します。

drop table if exists question_bank;

create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null,
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);

結論

SQL Serverにおける外部キーの設定は、データの整合性を確保し、テーブル間の関係を構築するための重要なステップです。外部キー制約を慎重に定義し、構文の問題を修正することで、堅牢で効率的なデータベース設計を作成できます。

エラーが発生した場合は、常に外部キーの定義を確認して、参照カラムが適切に対応しているかどうかを確認してください。このガイドを参考にすれば、SQL Serverデータベースで外部キーを成功裏に実装するための準備が整います。