In the Customer/Order database:
- Customer(custid, custname)
- Order(orderID, custid, OrderDate)
To ensure that there are no orphan records, we need to enforce referential integrity.
An orphan record is one whose foreign key value is not found in the corresponding entity – the entity where the PK is located. Recall that a typical join is between a PK and FK.
The referential integrity constraint states that the CustID in the Order table must match a valid CustiD in the Customer table. Most relational databases have declarative referential integrity. In other words, when the tables are created the referential integrity constraints are set up.
In the Course/Class database:
- Course(CrsCode, DeptCode, Description)
- Class(CrsCode, Section, ClassTime)
The referential integrity constraint states that CrsCode in the Class table must match a valid CrsCode in the Course table. In this situation, it’s not enough that the CrsCode and Section in the Class table make up the PK, we must also enforce referential integrity.
When setting up referential integrity it is important that the PK and FK have the same data types and come from the same domain. Otherwise the RDBMS will not allow the join.