Following the previous article on how to automate data quality tests in your database
, this article is about how to optimally establish your database controls to automatically check a SQL primary key constraint.
Let’s mention few prerequisites before starting with the main topic:
‘Constraints’ meaning: A constraint is usually associated with a table and is created with a CREATE CONSTRAINT or CREATE ASSERTION SQL statement. A common constraint is the primary key constraint:
‘Primary key’: Value(s) in specified column(s) must be unique for each row in a table and not be NULL; normally each table in a database should have a primary key – it is used to identify individual records. (source)
Besides database test automation (read about the importance of database unit testing), you should always follow the same pattern when designing databases. I’m thinking mostly of naming convention, used to increase readability of the model as well as rules used when adding a new table or changing existing ones. I use the following rules and I encourage you to do the same: