外部キーのリレーションシップの作成Create Foreign Key Relationships

適用対象: ○SQL Server (2016 以降) ○Azure SQL Database XAzure SQL Data Warehouse XParallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2016) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

このトピックでは、 SQL Server 2017SQL Server 2017 または SQL Server Management StudioSQL Server Management Studio を使用して、 Transact-SQLTransact-SQLで外部キーのリレーションシップを作成する方法について説明します。This topic describes how to create foreign key relationships in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. あるテーブルの行と他のテーブルの行を関連付ける場合は、2 つのテーブル間にリレーションシップを作成します。You create a relationship between two tables when you want to associate rows of one table with rows of another.

はじめにBefore You Begin! 制限事項と制約事項Limits and Restrictions

  • 外部キー制約からリンクを設定できるのは、別のテーブルの主キー制約だけではありません。別のテーブルの UNIQUE 制約が定義された列を参照するように定義することもできます。A foreign key constraint does not have to be linked only to a primary key constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

  • FOREIGN KEY 制約の列に NULL 以外の値を入力するときは、その値が参照される列に存在している必要があります。存在していないと外部キー違反のエラー メッセージが返されます。When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned. 複合外部キー制約のすべての値が検証されるようにするには、参加しているすべての列に NOT NULL を指定します。To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.

  • FOREIGN KEY 制約は、同じサーバー上の同じデータベース内のテーブルのみを参照できます。FOREIGN KEY constraints can reference only tables within the same database on the same server. 複数のデータベースにまたがる参照整合性は、トリガーを使って実装する必要があります。Cross-database referential integrity must be implemented through triggers. 詳細については、「CREATE TRIGGER (Transact-SQL)」を参照してください。For more information, see CREATE TRIGGER (Transact-SQL).

  • FOREIGN KEY 制約は、同じテーブル内の他の列を参照できます。FOREIGN KEY constraints can reference another column in the same table. これは、自己参照と呼ばれます。This is referred to as a self-reference.

  • 列レベルで指定された FOREIGN KEY 制約は、参照列を 1 つだけ表示できます。A FOREIGN KEY constraint specified at the column level can list only one reference column. この参照列は、制約が定義されている列と同じデータ型である必要があります。This column must have the same data type as the column on which the constraint is defined.

  • テーブルレベルで指定された FOREIGN KEY 制約は、制約列リスト内の列の数と同じ数の参照列を持っている必要があります。A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. また、各参照列のデータ型は、列リスト内の、参照列に対応する列と同じでなければなりません。The data type of each reference column must also be the same as the corresponding column in the column list.

  • データベース エンジンDatabase Engine には、他のテーブルを参照するテーブルに含めることができる FOREIGN KEY 制約の数についても、特定のテーブルを参照する他のテーブルが持つ FOREIGN KEY 制約の数についても、事前定義済みの制限はありません。The データベース エンジンDatabase Engine does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain that reference other tables, or the number of FOREIGN KEY constraints that are owned by other tables that reference a specific table. ただし、使用できる FOREIGN KEY 制約の実際の数は、ハードウェア構成やデータベースおよびアプリケーションのデザインにより制限されます。Nevertheless, the actual number of FOREIGN KEY constraints that can be used is limited by the hardware configuration and by the design of the database and application. テーブルから、他のテーブルと列を最大 253 個まで外部キーとして参照 (発信参照) することができます。A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x)SQL Server 2016 (13.x) では、1 つのテーブル内の列を参照 (着信参照) できる他のテーブルと列の数が 253 から 10,000 までに限られています。increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (少なくとも 130 の互換性レベルが必要です)。増加には、次の制限があります。(Requires at least 130 compatibility level.) The increase has the following restrictions:

    • 253 を超える外部キー参照は、DELETE と UPDATE DML 操作でのみサポートされています。Greater than 253 foreign key references are supported for DELETE and UPDATE DML operations. MERGE 操作はサポートされません。MERGE operations are not supported.

    • テーブル自体に対する外部キー参照も、253 の外部キー参照に制限されます。A table with a foreign key reference to itself is still limited to 253 foreign key references.

    • 現在、253 を超える外部キー参照は、列ストア インデックス、メモリ最適化テーブル、Stretch Database、では使用できません。Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, or Stretch Database.

  • FOREIGN KEY 制約は一時テーブルには設定されません。FOREIGN KEY constraints are not enforced on temporary tables.

  • CLR ユーザー定義型の列に対して外部キーを定義する場合は、型の実装でバイナリ順がサポートされている必要があります。If a foreign key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. 詳細については、「 CLR ユーザー定義型」を参照してください。For more information, see CLR User-Defined Types.

  • varchar(max) 型の列は、その列が参照する主キーも varchar(max) 型として定義されている場合にのみ FOREIGN KEY 制約で使用できます。A column of type varchar(max) can participate in a FOREIGN KEY constraint only if the primary key it references is also defined as type varchar(max).

アクセス許可Permissions

外部キーが設定された、新しいテーブルを作成するには、データベースの CREATE TABLE 権限と、テーブルを作成するスキーマの ALTER 権限が必要です。Creating a new table with a foreign key requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

既存のテーブルに外部キーを作成するには、テーブルに対する ALTER 権限が必要です。Creating a foreign key in an existing table requires ALTER permission on the table.

テーブル デザイナーで外部キー リレーションシップを作成するCreate a foreign key relationship in Table Designer

SQL Server Management Studio の使用Using SQL Server Management Studio

  1. オブジェクト エクスプローラーで、リレーションシップの外部キー側となるテーブルを右クリックして、 [デザイン] をクリックします。In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and click Design.

    テーブル デザイナーにテーブルが表示されます。The table opens in Table Designer.

  2. [テーブル デザイナー] メニューの [リレーションシップ] をクリックします。From the Table Designer menu, click Relationships.

  3. [外部キーのリレーションシップ] ダイアログ ボックスで、 [追加] をクリックします。In the Foreign-key Relationships dialog box, click Add.

    リレーションシップが [選択されたリレーションシップ] ボックスに表示されます。このリレーションシップには、FK_<tablename><tablename> (tablename は外部キー テーブルの名前) という形式の名前が自動的に割り当てられます。The relationship appears in the Selected Relationship list with a system-provided name in the format FK<tablename>_<tablename>, where tablename is the name of the foreign key table.

  4. [選択されたリレーションシップ] ボックスの一覧で、リレーションシップをクリックします。Click the relationship in the Selected Relationship list.

  5. 右側のグリッドの [テーブルと列の指定] をクリックし、その右側にある省略記号 ( [...] ) をクリックします。Click Tables and Columns Specification in the grid to the right and click the ellipses (...) to the right of the property.

  6. [テーブルと列] ダイアログ ボックスの [主キー] ボックスの一覧で、リレーションシップの主キー側となるテーブルをクリックします。In the Tables and Columns dialog box, in the Primary Key drop-down list, choose the table that will be on the primary-key side of the relationship.

  7. 下のグリッドで、テーブルの主キーになる列を選択します。In the grid beneath, choose the columns contributing to the table's primary key. 各列の左横のグリッド セルで、外部キー テーブルの対応する外部キー列を選択します。In the adjacent grid cell to the left of each column, choose the corresponding foreign-key column of the foreign-key table.

    リレーションシップの名前は、テーブル デザイナー によって割り当てられます。Table Designer suggests a name for the relationship. この名前を変更するには、 [リレーションシップ名] ボックスの内容を編集します。To change this name, edit the contents of the Relationship Name text box.

  8. [OK] をクリックしてリレーションシップを作成します。Choose OK to create the relationship.

新しいテーブルに外部キーを作成するCreate a foreign key in a new table

Transact-SQL の使用Using Transact-SQL

  1. オブジェクト エクスプローラーで、 データベース エンジンDatabase Engineのインスタンスに接続します。In Object Explorer, connect to an instance of データベース エンジンDatabase Engine.

  2. [標準] ツール バーの [新しいクエリ] をクリックします。On the Standard bar, click New Query.

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、[実行] をクリックします。Copy and paste the following example into the query window and click Execute. この例では、テーブルを作成し、Sales.SalesReason テーブル内の SalesReasonID 列を参照する外部キー制約を TempID 列に定義します。The example creates a table and defines a foreign key constraint on the column TempID that references the column SalesReasonID in the Sales.SalesReason table. ON DELETE CASCADE 句および ON UPDATE CASCADE 句を使用することによって、Sales.SalesReason テーブルに対する変更が自動的に Sales.TempSalesReason テーブルにも反映されるようにしています。The ON DELETE CASCADE and ON UPDATE CASCADE clauses are used to ensure that changes made to Sales.SalesReason table are automatically propagated to the Sales.TempSalesReason table.

    USE AdventureWorks2012;    
    GO    
    CREATE TABLE Sales.TempSalesReason (TempID int NOT NULL, Name nvarchar(50),     
    CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID),     
    CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)     
        REFERENCES Sales.SalesReason (SalesReasonID)     
        ON DELETE CASCADE    
        ON UPDATE CASCADE    
    );
    GO    
    
    

既存のテーブルに外部キーを作成するCreate a foreign key in an existing table

Transact-SQL の使用Using Transact-SQL

  1. オブジェクト エクスプローラーで、 データベース エンジンDatabase Engineのインスタンスに接続します。In Object Explorer, connect to an instance of データベース エンジンDatabase Engine.

  2. [標準] ツール バーの [新しいクエリ] をクリックします。On the Standard bar, click New Query.

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、[実行] をクリックします。Copy and paste the following example into the query window and click Execute. この例では、TempID 列に外部キーを作成し、Sales.SalesReason テーブルの SalesReasonID 列を参照します。The example creates a foreign key on the column TempID and references the column SalesReasonID in the Sales.SalesReason table.

    USE AdventureWorks2012;    
    GO    
    ALTER TABLE Sales.TempSalesReason     
    ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)     
        REFERENCES Sales.SalesReason (SalesReasonID)     
        ON DELETE CASCADE    
        ON UPDATE CASCADE    
    ;    
    GO    
    
    

    詳細については、「ALTER TABLE (Transact-SQL)」、「CREATE TABLE (Transact-SQL)」、および「table_constraint (Transact-SQL)」を参照してください。For more information, see ALTER TABLE (Transact-SQL), CREATE TABLE (Transact-SQL), and table_constraint (Transact-SQL).