Database Tutorial

Mandatory relationships are the general rule in database design. Mandatory relationships occur when the foreign key column or set of columns are also not null constrained.

A foreign key constraint only guarantees that the value in the column will be a value found in a list values in another column. A foreign key constrained column also can hold a null value unless you also make the foreign key column a not null constrained column.

This ERD shows you the relationships between four tables:

If you follow the logic of the Entity Relationship Diagram (ERD), the columns with a filled red diamond are mandatory relations. The model depicts the mandatory relations qualified in the bullet points:

• The member table:
  • A credit_card_type column value must be found in the valid list of the common_lookup table’s primary key column and must be not null constrained.
  • A member_type column value must be found in the valid list of the common_lookup table’s primary key, but the column initially accepts a null value because it isn’t constrained by a NOT NULL constraint.
• The contact table:
  • A member_id column value must be found in the valid list of the member table’s primary key member_id column; and the member_id column also must be constrained by a not null constraint.
• The address table:
  • A contact_id column value must be found in the valid list of the contact table’s primary key contact_id column; and the contact_id column also must be constrained by a not null constraint.
• The telephone table:
  • A contact_id column value must be found in the valid list of the contact table’s primary key contact_id column; and the contact_id column also must be constrained by a not null constraint.
  • A address_id column value must be found in the valid list of the address table’s primary key address_id column; and the address_id column is unconstrained or null allowed.

You should notice that most of the foreign key columns are red, which means they are not null constrained. That’s the general pattern when a row can’t exist in a dependent table without a mandatory relationship to the table holding the primary key value.