Defining Foreign Keys

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Exploring and Changing the Database Structure > Designing Tables in Design Editor >

Defining Foreign Keys

Foreign key constraints help preserve the integrity of the database. For example, 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 foreign key constraint 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 (for example, "Foreign key constraint failed").

 

You can create foreign key constraints either by writing a SQL query in SQL Editor, or you can design them visually from the Design Editor of DatabaseSpy. Note the following prerequisites:

 

The columns that take part in the foreign key relationship must be of the same data type and length. In this example, it is assumed that both the column DEPARTMENT.ID and PERSON.DEPARTMENT_ID are of type "int".
The referencing column (DEPARTMENT.ID, in this example) must be part of either a primary key or unique key.

 

Note:In SQL Server, you can also add a foreign key 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 foreign key in the Design Editor.

 

Approach 1

1.Add to the Design Editor both the table on which the foreign key should be and the referenced table.
2.Do one of the following:
oDrag a column from the Columns section of a table design (or from the Online Browser) and drop it onto the Keys section of a table design.
oDrag a primary key, unique key, or (in SQL Server) unique index from a table design (or from the Online Browser) and drop it onto the Keys section of a table design.

 

By default, a foreign key having the name <generated> is automatically created on the first column that has the same data type as the column that is referenced by the dragged key or index, respectively. This may or may not be the column on which you need the foreign key. 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.Do one of the following:
oClick the Create new Key Constraint ic_add-column icon in the Keys section of a table design.
oRight-click anywhere into the table design and select Insert new | Key | Foreign Key from the context menu.

 

A foreign key 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.If applicable, set the "from" and "to" columns for the foreign key, as follows:

 

oTo change the column on which the foreign key 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 foreign key points, double-click the column (in the Keys section, under "Reference"), and select the appropriate entry from the list.

 

Naming foreign keys

By default, the name of the foreign key 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 the generated name. To do this, enter a descriptive name in place of <generated>, and press Enter.

 

If you override the generated name with a custom name, the new name must be unique; otherwise, errors will occur when the database change script is executed.

 

Note:In SQLite, custom key names are ignored by the database. The key name displayed in the Online Browser of DatabaseSpy is the internal key name as provided by the database.

 

Duplicating foreign keys

If you want to duplicate an existing foreign key, you can generate an SQL statement that adds a foreign key constraint to the table, as follows.

 

1.In the Online Browser, expand the Keys folder of the table that contains the foreign key you want to duplicate and right-click the foreign key.
2.Select Show in new SQL Editor | Add from the context menu.
3.In the SQL Editor, edit table name, column name, as well as the name of the foreign key as required.
4.Click the Execute ic_execute-sql button to execute the SQL statement and create the new foreign key.

 

Cross-schema references

Foreign keys can also reference a column in a table that is located in a different schema. When defining this reference using the Design Editor, tables in the same schema are printed black, whereas tables located in a different schema are printed blue in the drop-down list that opens when you double-click a table name in the Reference column of a table design.

design_cross-schema-FK


© 2019 Altova GmbH