Creating Foreign Keys
As with other database objects, there are several ways to create foreign key constraints:
•From the Online Browser
•From the Design Editor
•By generating SQL from an existing foreign key object
Prerequisites
When creating foreign keys, note the following prerequisites:
•The columns that take part in the foreign key relationship must be of the same data type and length.
•The referencing column (Department.ID, in this example) must be part of either a primary key or unique key.
Also, note the following database-specific behavior:
•In SQL Server, you can add a foreign key reference to a column not only if it's part of a primary or unique key but also 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).
•SQLite does not support adding foreign keys to existing tables. You can create foreign key constraints only at table creation time.
Preparing the database
The instructions below present various ways to create a foreign key relationship between two tables. We will use the tables discussed in the previous example, Employee and Department, and a SQL Server database. The instructions are similar in other databases. If you want to follow the instructions below literally, you can prepare your SQL Server database by running the following script:
CREATE TABLE |
Table creation scripts (SQL Server)
In the SQL script above, notice that the Department.ID column is a primary key. Also, the columns Employee.DepartmentID and Department.ID are both of type integer, which meets the prerequisites for creating a foreign key relationship between these two columns.
Creating foreign keys in the Online Browser
1.Right-click the Employee table and select Create Key | Create Foreign Key from the context menu. The foreign key appears in the "Keys" folder, with the name <generated>. Also, a script is generated in the Database Structure Change Script Window. At this stage, you can still revert the changes by clicking the Refresh 
button.
2.Optionally, select the <generated> name and press F2 to rename it.
3.Double-click "Choose a table" and browse for the foreign table. In this example, the foreign table is Department. Note that the ID column is selected automatically, because it is the column on which the primary key is.
4.Click the Execute Change Script 
button in the Database Structure Change Script window.
Creating foreign keys in Design Editor (approach 1)
1.Add both tables to the Design Editor (Employee and Department).
2.Do one of the following:
oDrag the DepartmentID column from the "Columns" section of the Employee table (or from the Online Browser) and drop it onto the "Key" section. Select Create Foreign Key from the context menu when prompted.
oDrag the primary key from the "Key" section of the Department table (or from the Online Browser) and drop it onto the "Key" section of the Employee table.
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 "Key" section) and select the correct column from the list.
3.Click the Execute Change Script button in the Database Structure Change Script window.
Creating foreign keys in Design Editor (approach 2)
1.Do one of the following:
oClick the Create new Key Constraint 
icon in the "Key" section of a table design, and then select Create Foreign Key from the context menu.
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.
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 "Key" section, under "Columns") and select the appropriate entry from the list.
oTo change the column to which the foreign key points, double-click the column (in the "Key" section, under "Reference"), and select the appropriate entry from the list.
3.Click the Execute Change Script button in the Database Structure Change Script window.
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. |
Creating foreign keys by generating SQL
To create a foreign key, you can also generate an SQL statement using an existing foreign key as a model, as follows.
1.In the Online Browser, expand the "Keys" folder of the table that contains the existing foreign key.
2.Right-click the foreign key and select Show in new SQL Editor | Add from the context menu. Note that this command is disabled if the database does not support altering the structure of existing tables.
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 
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, for example:
