labs / tiddlers / content / labs / lab03 / _Labs_03_Database Integrity

Main Concepts:

 Primary key constraints
 Foreign key constraints
 CHECK constraints
 Column vs table constraints
 Table creation order
 Constraint naming
 Testing constraints
 Importing data


A database with a well-designed structure that is centrally stored and managed is a good start, but there are still some potential problems with your database as it currently stands. For example, what happens if you try to add the same scientist more than once? What happens if you try to record a sample for a site that does not exist? The system should disallow these sorts of invalid data, and the key to enforcing this is integrity constraints.

SQL DBMSs support a number of kinds of constraint, which can be built into your database via extra code in your CREATE TABLE statements:

NOT NULL for mandatory columns (which you looked at last week)
PRIMARY KEY and UNIQUE for identity columns
FOREIGN KEY for referential integrity (orphan prevention)
CHECK for miscellaneous “business rules”

You will be extending the SQL schema definition script you created earlier, so start by making a copy of that file in a new folder for this week’s lab and opening the new copy in DBeaver.