An orphaned record is a record whose foreign key value references a non-existent primary key value.

Orphaned records are a concept within database relationships. If a row in a related table references a non-existent row in the primary table, it is said to be an orphaned row. This is because it has no “parent” with which its data is associated with. The terms orphaned row and orphaned record tend to be used interchangeably, even though there’s a subtle difference between a row and a record.

Continue reading →

Referential integrity refers to the accuracy and consistency of data within a relationship.

In relationships, data is linked between two or more tables. This is achieved by having the foreign key (in the associated table) reference a primary key value (in the primary – or parent – table). Because of this, we need to ensure that data on both sides of the relationship remain intact.

So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.

Continue reading →

The term data integrity refers to the accuracy and consistency of data.

When creating databases, attention needs to be given to data integrity and how to maintain it. A good database will enforce data integrity whenever possible.

For example, a user could accidentally try to enter a phone number into a date field. If the system enforces data integrity, it will prevent the user from making these mistakes.

Maintaining data integrity means making sure the data remains intact and unchanged throughout its entire life cycle. This includes the capture of the data, storage, updates, transfers, backups, etc. Every time data is processed there’s a risk that it could get corrupted (whether accidentally or maliciously).

Continue reading →

In database terminology, field is often used to refer to the individual cells within a row or column. However, it can also refer to the whole column itself.

When referring to an individual cell, we’re usually referring to the value within that cell. So a user might ask “what value is in the FirstName field?” when referring to an individual record.

When referring to the whole column, we’re usually referring to the name of the column, its data type, constraints, and any data contained within that column.

Continue reading →