In this topic, you will learn how to create foreign constraints in the database in Round-Trip Scenario.
In the How to: Create One-to-Many Associations, you learnt that if you create two entities, add an association between them and create/migrate your database to the latest model state, then a new foreign key constraint will be automatically created in the DDL script. Suppose, you have the following domain model.
There are two entities - Product and Category, without associations between them. Both of the entities are mapped to corresponding tables in the database. It doesn't matter how the tables are created, i.e. they could be created either in Database First Scenario or in Model First Scenario.
Suppose, you create a new association in the Visual Designer and migrate (update) your database by using the Update Database from Model wizard. This will not automatically lead to a foreign key constraint to be created in the database. Telerik Data Access will work fine even with an association that does not match a constraint. However, you can explicitly add the matching constraint through the Constraint Editor. After that it will be created in the database by the Update Database from Model wizard.
In summary, if you create the model with all associations and then create the database in a single trip, all the constraints will be created automatically. But, if you create the entities and the association in separate trips, this will not automatically lead to a foreign key constraint in the database.
The first step is to create a new association between the Product and Category entity. You could follow the pattern described in the How to: Create One-to-Many Associations. At this point, if you migrate (not create) your database, the generated DDL script will not create a matching foreign key constraint. So, you need to explicitly create the constraint:
- In the Visual Designer, open the Model Schema Explorer.
Right-click, the Constraints node and select Add Constraint.
This will open the Constraints Editor.
- For a Source Table, select the child table, i.e. this is the table with the foreign key.
- Respectively, for a Target Table, select the parent table. In case of this example, this is the Category table.
Select the foreign key from the Source Table, and the primary key from the Target table.
Click OK to create the constraint.
Save your model and run the Update Database from Model wizard to generated the migrate DDL script. Now the generated script will create the foreign key constraint in the database.
CREATE INDEX [idx_product_CategoryId] ON [product]([CategoryId]) GO ALTER TABLE [product] ADD CONSTRAINT [ref_product_category] FOREIGN KEY ([CategoryId]) REFERENCES [category]([CategoryId]) GO