Now that we have examined how to work with DBMS features, and how to select data types, let’s look at database integrity, including the usefulness of features like default values and null values.
Default Values: Ensuring There's Always Data
One great feature of most database management systems is the ability to require that fields be populated with data as well as predetermined values if no data is present when the record is saved. You could use not applicable or not available and abbreviate the entry with n/a, but that isn’t the purpose of default values. They are often used when you have a known default answer that is frequently left blank. It provides useful data and decreases the use of null values.
Can I use default values for most fields?
No. Default values for the majority of fields, such as a person’s name, birthdate, and zip code, would not make sense. They are based on the business rules or the standard operating procedures for an organization where default values would make sense. Normally, only a small number of fields would have a specified default value like the date a record is created. The user would not need to enter a value as the table would automatically place the timestamp for the day and time the record was created, which is a common practice.
Null Values: Representing a Lack of Data
Null values, another common feature in virtually any database, are placeholders. Remember that they represent the absence of data and are an indicator that no data is present.
Why is this important?
It allows the user to know whether or not any data has been entered into the field. This can be useful in troubleshooting where only the fields in the table are populated when data actually exists. The individual troubleshooting an application issue could use the null value to determine whether or not that particular field was updated or not. This can help find a potential bug in the application writing data to the database.
PublisherID | Name | Phone | Website | |
1 | American University in Cairo Press | NULL | NULL | |
2 | Michigan State University Press | NULL | ||
3 | Westminster John Knox Press | 5551234 | NULL | NULL |
Referential Integrity: The Glue Holding a Database Together
Primary and foreign keys must match or be in agreement with one another. Remember that foreign keys are meant to refer back to the primary keys of a different entity or table.
What does this really mean?
If something changes with the primary key, then those changes must be passed down to any present foreign key, or no changes are made to any foreign key for consistency.
Orphan records are the result of foreign keys in one table that have no matching primary key in another table. This will cause issues with the accuracy of the data and empty values when records are queried.
Most DBMS will alert the user to this sort of situation. For example, you have a ProductColor table and delete the ColorID of 3, which is blue. The DBMS will not allow the record to be deleted until the ColorID in your Product table is changed; in other words, the DBMS will prevent the Hibble record in the example below from turning into an orphan record.
ProductColor Table
ColorID | Color |
1 | Red |
2 | Yellow |
3 | Blue |
Product Table
ProductID | ProductName | ColorID |
101 | Shibble | 1 |
102 | Hibble | 3 |
103 | Flibble-oo | 2 |
Let’s Recap!
Default values are used when there is no data in an attribute, or a very common condition occurs. The DBMS automatically inserts the default value if none is provided.
Null values are placeholders in a table and indicate no data has been written to a particular attribute for a given record.
Referential integrity ensures the foreign key always links back to a primary key in another table.
Once we've ensured the integrity of our database, we can consider optimizing it with an index.
