SQLShack (日本語)
以前の記事では、一般的に使用されるSQL Serverの制約：NULLではなく、ユニークで主キー、我々は、詳細に、SQL Serverの制約の最初の三つのタイプを説明しました。NULL、一意と主キーではありません。 この記事では、外部キー、チェック、およびデフォルトの三つの制約について、それぞれを簡単に説明し、実用的な例を提供して説明します。,
外部キー制約
外部キーは、二つのテーブルをリンクするために使用されるデータベースキーです。 FOREIGN KEY制約は、外部キーを含む子表の列または列のセットを、親表の主キー列または列のセットに参照することによって、データベース表間の関係を識別します。
子テーブルと親テーブルの関係は、これらの値を子テーブルに挿入する前に、参照される親テーブルの主キーに子テーブルの外部キー値が存在するかどうかをチェックすることによって維持されます。, このようにして、親テーブルの主キーを参照する子テーブルの外部キー制約により、データベース参照整合性が強制されます。 参照整合性は、データ挿入プロセス中にデータベーステーブル間の関係が保持されるようにします。 PRIMARY KEY制約は、選択した列または列のNULL値または重複値がそのテーブルに挿入されないことを保証し、そのテーブルのエンティティ整合性を強制すること, 主キーによって強制されるエンティティ整合性と外部キーによって強制される参照整合性が一緒にキー整合性を形成します。
FOREIGN KEY制約はPRIMARY KEY制約とは異なり、各テーブルごとに一つの主キーのみを作成でき、複数の親テーブルを参照することによって各テーブルに複数の外部キー制約を作成できるという点で異なります。 別の違いは、このキーにNOT NULL制約が定義されていない場合、外部キーはNULL値を挿入できるが、主キーはNullを受け入れないということです。, FOREIGN KEY制約を使用すると、on UPDATE句およびON DELETE句を使用して、親テーブル内の参照値が更新または削除されたときに実行されるアクションを制御すること 親テーブルの値を削除または更新するときに実行できるサポートされているアクションは次のとおりです。
- NO ACTION:ON UPDATE句またはON DELETE句がNO ACTIONに設定されている場合、親テーブルで実行されたupdateまたはdelete操作はエラーで失敗します。,
- CASCADE:ON UPDATE句またはON DELETE句をCASCADEに設定すると、親テーブルの参照値に対して実行された同じアクションが子テーブルの関連値に反映されます。 たとえば、参照先の値が親テーブルで削除された場合、子テーブル内の関連するすべての行も削除されます。
- SET NULL:このON UPDATEおよびON DELETE句オプションを使用すると、親テーブル内の参照値が削除または変更されると、子テーブル内の関連するすべての値がNULL値,
- SET DEFAULT:ON UPDATE句およびON DELETE句のSET DEFAULTオプションを使用すると、親テーブル内の参照値が更新または削除された場合、外部キー列を持つ子テーブル内の関連値がデフォルト値に設定されることを指定します。 CREATE TABLE T-SQL文を使用して列を定義するときに外部キー制約を追加したり、ALTER TABLE T-SQL文を使用して表の作成後に追加したりできます。 外部キー制約機能を理解するために、二つの新しいテーブルを作成します。, 最初のテーブルは、ID列が主キー列として定義された親テーブルとして機能します。 第二のテーブルは子テーブルとして機能し、ID列は親テーブルのID列を参照する外部キー列として定義されます。,>ID INT主キー,
二つのテーブルを作成した後、次のinsertステートメントを使用して、親テーブルに三つのレコードを挿入し、子テーブルに二つのレコードを挿入します。
結果は、三つのレコードが親テーブルに正常に挿入されたことを示します。
結果は、三つのレコードが親テーブルに正常, 子テーブルに挿入しようとした最初のレコードは、ID値1がすでに親テーブルに存在するため、エラーなしで挿入されます。,ID値4は親テーブルに存在せず、外部キー制約のため、親テーブルに存在しない子テーブルにID値を挿入することはできません。
親テーブルと子テーブルの内容を確認すると、
子テーブルの作成中に外部キー制約名については言及していないため、sql SERVERはinformation_schemaから取得できる一意の名前を割り当てます。,TABLE_CONSTRAINTS system view using the following query:
|
1
2
3
4
5
6
7
8
|
SELECT CONSTRAINT_NAME,
TABLE_SCHEMA ,
TABLE_NAME,
CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.,onstraintDemoChild
ADD CONSTRAINT Fk__ConstraintDe__ID
FOREIGN KEY(ID)REFERENCES ConstraintDemoParent(ID);
|
ID値4が親テーブルに存在せず、参照整合性が壊れるため、操作は失敗します
子テーブルに外部キー制約を作成できるようにするには、まずそのレコードを削除または更新することによって、その競合を排除す, Here we will try to modify the ID value with the following UPDATE command:
|
1
2
3
|
UPDATE ., SET ID=2WHERE ID=4
|
以下に示すように、外部キー制約はエラーなしで正常に作成されます。
外部キー制約はSQL Server Management Studioツールの助けを借りて定義できます。 必要なテーブルを右クリックし、デザインオプションを選択します。, そして最後に、参照レコードが親テーブルで削除または変更されたときに実行されるアクションを次のように指定できます。
CHECK Constraint
CHECK constraintは、列または列のセットに定義され、範囲を制限することができます。定義済みの条件を使用して、これらの列に挿入できる値のうち、値の値を指定します。, チェック制約は、挿入または変更された値を評価するために実行され、条件を満たす値がテーブルに挿入され、そうでない場合は挿入操作が破棄されま 同じ列に対して複数のCHECK制約を指定することができます。
CHECK制約条件を定義することは、AND、OR、BETWEEN、IN、LIKE、IS NULLなどの異なる比較演算子を使用して、TRUE、FALSE、またはUNKNOWNを返すブール式を記述するために、クエリのWHERE句を記述することと何らかの形で似ています。, チェック制約は、条件にNULL値が存在する場合、未知の値を返します。 CHECK制約は、挿入された値を定義された値、範囲、または形式の規則に従う値に制限することによって、ドメインの整合性を強制するために主に使用さそのテーブルの主キーとみなされるID列、Name列、Salary列を作成します。 Salary列にCHECK制約が定義されており、その列にゼロまたは負の値が挿入されないようにします。,v>6
以下の三つのinsertステートメントを実行すると、
指定された給与値がチェックを満たしているため、最初のレコードがエラーなしで挿入されることは、生成された結果から明らかです。
コンディション,次のエラーメッセージに示されているように、給与値がゼロと負の給与値を挿入するためにチェック制約条件を満たしていません。
テーブルのデータをチェックすると、チェック制約条件に合格した最初の行のみが以下のようにテーブルに挿入されます。
この記事の前半では、参加した列でnullが許可されている場合、check制約でnull値の挿入が許可され、null値はエラーをスローすることなくunknownとして評価されます。, これは、Salary列の指定された値がNULLですが、正常に挿入された以下のレコードから明らかです。
前のCREATE TABLEステートメントでCHECK制約定義を確認すると、定義済みの制約の名前が言及されていないことがわかります。 この場合、SQL Serverは、INFORMATION_SCHEMAを照会することによって表示できる、その制約に一意の名前を割り当てます。ConstraintDemo4テーブルのTABLE_CONSTRAINTSシステムビュー。, 結果は以下のようになります。
チェック制約は、ALTER TABLE T-SQL文を使用して削除できます。,onstraint now, with the following ALTER DATABASE … ADD CONSTRAINT T-SQL statement:
|
1
2
3
4
5
|
ALTER TABLE ConstraintDemo4
ADD CONSTRAINT CK__Constrain__Salar
CHECK (Salary>0);
|
Adding the CHECK constraint will fail., これは、SQL Serverがチェック制約条件の既存のデータをチェックしている間に、次のエラーメッセージに示すように、制約条件を満たさない値が見つかるためです。
チェック制約を再度定義できるようにするには、制約が作成されないようにするデータを変更または削除する必要があります。,de484761″>
以下のように、チェック制約を問題なく定義できます。
sql serverでは、テスト目的で制約条件を満たすことを気にせずに大量のデータを挿入するなど、特別なケースでチェック制約を無効にすることができます。, このケースは、一時的に無効にできるCHECK制約およびFOREIGN KEY制約に対してのみ有効です。,ritingの制約においては、これまでのT-SQLでは以下のコマンド
|
1
2
3
|
ALTER TABLE ConstraintDemo4NOCHECK制約すべての
|
チェックインから制約の定義を指定することができますが、制作チェックせずに、既存の行 その制約が記載されていない信頼を得ています。, 以下のT-SQLコマンドを使用して、すべてのチェック制約を一度に有効にできます。
|
1
2
3
|
alter table constraintdemo4check constraint all
|
前のチェック制約を有効にすると、sql serverはチェックされていないデータについて文句を言わないことがわかります。制約条件。, この場合、データのドメイン整合性は維持されません。
この問題を解決するには、DBCC CHECKCONSTRAINTSコマンドを使用して、指定したテーブルまたは制約の制約条件に違反するデータを簡単に識別できます。,v id=”ff419aa09d”>
結果には、以下に示すように、チェック制約条件を破る二つの給与値が表示されます。
必要なテーブルを右クリックして設計オプションを選択することにより、SQL SERVER MANAGEMENT studioツールの助けを借りてチェック制約を作成することもできます。, 表示されたデザインウィンドウで、右クリックしてCheck Constraintsオプションを選択します。
Check Constraintsウィンドウでは、以下のようにCHECK constraint名と式を指定し、既存のデータをチェックする必要がある場合は、
DEFAULT Constraint
デフォルトの制約を使用して、チェック制約名を指定できます。insertステートメントでその列に値が指定されていない場合は、挿入された行にデフォルトの列値を指定します。, 既定の制約は、ユーザーが列に値を指定しない場合に、列に適切な値を指定することによって、ドメインの整合性を維持するのに役立ちます。 デフォルト値には、定数値、システム関数値、またはNULLを指定できます。,elow:
|
1
2
3
4
|
alter table constraintdemo5
add constraint df__constrain__emplo default(getdate())for employeedate
|
また、既定の制約は、sql server management studioを使用して、必要なテーブルを右クリックして[デザイン]オプションを選択することで定義できます。, 次に、次に示すように列のプロパティウィンドウを参照して、既定値を割り当てる列を選択します。
この記事と前の記事が、sql Server制約の種類の説明に役立つことを願っていました。 以下のコメントにご質問をお気軽にお願いいたします。,sql Serverの制約を使用していません：NULL、一意、および主キーではありません
便利なリンク
- チェック制約の作成
- 制約
- デフォルト
- 外部キー関係の作成
- 作成者
- 最近の投稿
彼は、データ管理と分析のマイクロソフト認定ソリューションの専門家、SQLデータベースの管理と開発のマイクロソフト認定ソリューションアソシエイト、Azure
また、彼は多くのブログで彼のSQLのヒントに貢献しています。,
Ahmad Yaseenによるすべての投稿を表示する
- Azure Data Factoryのソース管理を使用する方法-January15,2021
- Azure Data Factoryのソース管理を使用する-January12,2021
- Azureでテンプレートを使用するデータファクトリー-January8,2021