A relationship will generate a Foreign Key or Referential Constraint in a Physical Data Model.
How exactly this constraint is generated depends on the relationship type.
If you selected the attributes that are part of the relationship, no additional columns will be added to the tables involved. If you leave the attributes set to [default], new columns will be created and added to the tables.
When creating new columns, these will be NOT NULL, depending on the "required" status of the relationship.
With a one-to-one relationship, it depends on the "direction" or "dominant role" from which table to which table the constraint is "pointing". If you did not define the attributes that make up the relationship yourself, the dominant role is used to decide in which table to add the foreign column.
Take a look at the following example.
One-to-one relationship, Person as a "base class" for Employee
In the above screenshot, you can say that "Person" is over "Employee", without a person, there will be no employee. This will generate a foreign key constraint as follows:
Foreign Key from Employee to Person
This generates the column PERSONID into table Employee. If the relationship is an "identifying" relationship, the newly generated column will be added to the primary key of the child table.
One-to-many or many-to-one relationship
These relationships are the same, only the direction differs. This is a common relationship, see the example below.
A department has zero or multiple employees, while each employee works at 1 department
Each employee works at a department, this is a required item. This means that the column for Department will be NOT NULL. It's a non-identifying relationship, so the new column will not be part of the primary key of the Employee table.
The result is a simple foreign key constraint:
A foreign key from Employee to Department
A many to many relationship cannot be generated by creating a foreign key from the source to destination table. In the example below, an "Online Order" can contain multiple "Products", while any "Product" can be used by multiple "Online Orders".
Example of a many-to-many relationship
The many-to-many relationship will be "solved" by using an intermediate table named after the relationship that contains both the identifier from the source and destination table. This combined set of identifier attributes now forms the primary key of the intermediate table.
Generated tables and foreign key constraints