Introduction to Foreign Keys

Foreign keys are a fundamental concept in relational databases. They establish a relationship between two tables by linking a column in one table to a primary key column in another table. This relationship ensures data integrity and enables the enforcement of referential integrity constraints.

By using foreign keys, you can create relationships between tables, enforce data consistency, and enable cascading updates and deletes. Foreign keys play a crucial role in maintaining the integrity and coherence of a database.

Foreign keys in Neos

When an new relationship (References and collections) is created between two entities, the corresponding foreign key should be created. To create it automatically, run the action Generate metadata. You can modify it if necessary. Otherwise, you can create them manually. The naming convention is FK_{ReferencingTableName}_{NavigationPropertyName}.

Example : FK_Order_InvoicedCustomer

classDiagram
    Order --|> Customer : InvoicedCustomer (Navigation property)
    class Customer{
        +Id : int
    }
    class Order{
        +InvoicedCustomerId : int
    }

Performance and indexes

By default, a foreign key doesn't have a corresponding index. If the volume of data becomes large, there will be an impact on performance. With Neos, by default, all foreign keys are created with an automatic index.

If an index starting with the same columns as the foreign key already exists, then no automatic index will be created. A warning informs the developer that an index already exists :

• Remove the manual index. Before, check that the index is identical.
• Switch the property Automatic index to false on the foreign key. For example, if the index contains more properties than necessary and is not specific to the foreign key.

Finally, if the index is not needed, you can choose to deactivate it manually. This makes the model lighter because unused indexes can have a negative impact on performance.