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.
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.
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).
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.
In relational databases, a record is a collection of fields that contain data about a given entity.
A record is typically stored as a row in a table. A record contains the smallest amount of data that can be inserted, updated or deleted from a table.
An example of a record could be a single row in a “Customers” table. This row could contain the customer’s first name and last name for example. Therefore, the record contains the customer’s first name and last name. It could also contain other fields as required – such as an ID field, the date the record was created, etc.
In a relational database, a column is a vertical group of cells within a table.
It’s also used to describe the vertical group of cells within a result set of a query, or other database objects, such as views, stored procedures, table-valued functions, etc.
In a table, each column is typically assigned a data type and other constraints which determine the type of value that can be stored in that column. For example, one column might email addresses, another might accept phone numbers.
In relational database terms, a row is a collection of fields that make up a record.
The cells in a row run horizontally, and together, contain all data for that record.
A row can contain as many fields as required, each one defined in a different column. There must be at least one column defined in a table before a row of data can be added. The row is the smallest unit of data that can be inserted into a table and deleted from a table.
In database terminology, a cell is a part of a table where a row and column intersect. A cell is designed to hold a specified portion of the data within a record. A cell is sometimes referred to as a field (although a column is also often referred to as a field).
A table row is made up of one or more cells running next to each other horizontally. A column is made up of one or more cells running below each other vertically.
A database table is a structure that organises data into rows and columns – forming a grid.
Tables are similar to a worksheets in spreadsheet applications. The rows run horizontally and represent each record. The columns run vertically and represent a specific field. The rows and columns intersect, forming a grid. The intersection of the rows and columns defines each cell in the table.
A database is a collection of data, stored in a logical and structured manner.
The way in which data is organised, allows for efficient retrieval of the data. Data can be viewed, inserted, updated, and deleted as required.
Most modern databases are built with database software such as Microsoft Access, SQL Server, MySQL, etc. But strictly speaking, a database could be a simple as an Excel spreadsheet or even a text file.