It is a common error to assume that you need to explicitly define foreign keys in the Entity Relationship Diagram (ERD). The concept of the ERD, is that the relationships implicitly define the placement of foreign keys, and the user need not explicitly do so. If you explicitly add a foreign key column to the ERD and then generate a Schema or Data Structure Diagram, Database Design Studio will generate the implicit foreign key and append a digit, 1..N to avoid column name conflicts.
To create the foreign keys correctly, consider the following steps, starting with the ERD:
In the above conceptual model, we would like the primary key of the Customer entity to be a foreign key in the PurchaseOrder entity. This is achieved by defining the correct type of relationship. In this case, we have created a 1:M relationship between Customer and PurchaseOrder with the following parameters:
Notice the Connectivity of 1:M and the Min and Max Participation settings. The 1:M relationship indicates that the primary key of the entity on the "1" side will be placed into the entity on the "M" side as a foreign key when the schema or logical model is generated. The Participation controls the nullability and foreign key constraint behaviour.
In our sample, the Customer entity has the following attribute:
and the PurchaseOrder entity has the following attribute:
Notice that the Customer primary key CustID does not appear in the PurchaseOrder attribute list within the ERD model properties editor.
Now consider the Data Structure Diagram (DSD) that Database Design Studio generates from the above ERD:
Notice that in this logical model, the CustID has implicitly been placed into the object on the Many side of the relationship as a foreign key. If you open the DSD properties editor for the PurchaseOrder object, you will notice that the CustID has likewise been included:
The position of the columns can be ordered using the method of ordering rows in the grid views.
See also: Foreign Primary Keys and Renaming Foreign Key Columns