Primary key constraints
Foreign key constraints
Column vs table constraints
Table creation order
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.