Defining 外部キー

www.altova.com このトピックを印刷 前のページ 1つ上のレベル 次のページ

ホーム >  データベース構造の探索と変更 > デザインエディター内でのテーブルのデザイン >

Defining 外部キー

外部キー制約s help preserve the integrity of the database. 例えば、 let's assume that you are designing a database to manage employees in a company. The employees are stored in the PERSON table and the departments are stored in the DEPARTMENT table.

 

+----+------------------+--------------------------+---------------+

| ID | NAME             | EMAIL                    | DEPARTMENT_ID +

+----+------------------+--------------------------+---------------+

| 1  | Toby Hughey      | t.hughey@nanonull.com    | 1             |

| 2  | Mia Dahill       | m.dahill@nanonull.com    | 2             |

| 3  | Fred Weinstein   | f.weinstein@nanonull.com | 1             |

+----+------------------+--------------------------+---------------+

The PERSON table

+----+-------------+

| ID | NAME        |

+----+-------------+

| 1  | Development |

| 2  | Marketing   |

+----+-------------+

The DEPARTMENT table

Note that the DEPARTMENT_ID in the PERSON table points to the ID of the department in the DEPARTMENT table. This relationship can be enforced through a 外部キー制約 between these two columns, so as to ensure that:

 

Each person record in the PERSON table can reference any of the existing departments in the DEPARTMENT table (one to one relationship).
If you ever attempt to delete a record from the DEPARTMENT table, and if there are PERSON records pointing to that DEPARTMENT, the database would return a legitimate validation error (例えば、 "外部キー制約 failed").

 

You can create 外部キー制約s either by writing a SQL query in SQL エディター, or you can design them visually from デザインエディター ウィンドウ of DatabaseSpy. Note the following prerequisites:

 

The columns that take part in the 外部キー relationship must be of the same データ型 and length. In this example, it is assumed that both the column DEPARTMENT.ID and PERSON.DEPARTMENT_ID are of type "int".
The 参照する列 (DEPARTMENT.ID, in this example) must be part of either a primary key or 一意キー.

 

メモ:In SQL Server, you can also add a 外部キー reference to a column if there is a unique index defined on it. This is also possible in newer versions of MySQL (provided that the storage engine is InnoDB).

 

There are several ways to define a 外部キー in デザインエディター ウィンドウ.

 

Approach 1

1.Add to デザインエディター ウィンドウ both the table on which the 外部キー should be and the referenced table.
2.次のいずれかを実行してください:
oDrag a column from the Columns section of a table design (または、オンラインブラウザーから) and drop it onto the Keys section of a table design.
oDrag a primary key, 一意キー, or (in SQL Server) unique index from a table design (または、オンラインブラウザーから) and drop it onto the Keys section of a table design.

 

デフォルトでは、 a 外部キー having the name <generated> is automatically created on the first column that has the same データ型 as the column that is referenced by the dragged key or index, 個々のly. This may or may not be the column on which you need the 外部キー. Therefore, if applicable, double-click the column (in the Keys section, under "Columns") and select the correct column from the list.

dbs_fk_approach1

 

Approach 2

1.次のいずれかを実行してください:
oClick the Create new キー制約 ic_add-column アイコン in the Keys section of a table design.
oRight-click anywhere into the table design and select 「新規の挿入 | Key | 外部キー をコンテキストメニューから選択します。

 

A 外部キー having the name <generated> is created automatically, without a reference to a particular table. To choose the referenced table, click [select the referenced table] and select the required table from the list.

dbs_fk_approach2a

2.適応できる場合、 set the "from" and "to" columns for the 外部キー, as follows:

 

oTo change the column on which the 外部キー is, double-click the column (in the Keys section, under "Columns") and select the appropriate entry from the list.

dbs_fk_approach2b

oTo change the column to which the 外部キー points, double-click the column (in the Keys section, under "Reference"), and select the appropriate entry from the list.

 

Naming 外部キー

デフォルトでは、 the name of the 外部キー is <generated>, which means its name will be automatically generated by the database. This ensures uniqueness of the name in the database. If necessary, you can override 生成された名前. To do this, enter a descriptive name in place of <generated>, and press Enter.

 

If you override 生成された名前 with a custom name, the new name must be unique; otherwise, errors will occur when the database change script is executed.

 

メモ:In SQLite, custom key names are ignored by the database. The key name displayed in the オンラインブラウザー of DatabaseSpy is the internal key name as provided by the database.

 

Duplicating 外部キー

If you want to duplicate an existing 外部キー, you can generate an SQL statement that adds a 外部キー制約 to the table, as follows.

 

1.オンラインブラウザー内で、 expand the Keys folder of the table that contains the 外部キー you want to duplicate and right-click the 外部キー.
2.Select 「新規の SQL エディターで表示 | Add をコンテキストメニューから選択します。
3.SQL エディター内で、 edit table name, 列名, as well as the name of the 外部キー as required.
4.Click the 実行 ic_execute-sql ボタン to execute the SQL statement and create the new 外部キー.

 

Cross-schema references

外部キー can also reference a column in a table that is located in a different schema. When defining this reference using デザインエディター ウィンドウ, tables in the same schema are printed black, whereas tables located in a different schema are printed blue in the ドロップダウンリスト that opens when you double-click a table name in the Reference column of a table design.

design_cross-schema-FK


(C) 2019 Altova GmbH