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:
It’s important to notice that all three tables have primary keys defined following the naming convention mentioned before. For each table in our model I want to test:If the answer to both questions is “Yes”, then all primary keys are set in our database properly. I’ll use the following stored procedure to perform this test: p_check_PK. The only parameter I’ll pass to the procedure is the name of the database I want to test. My database is called “simple_db” so I’ll use this call: CALL p_check_PK (“simple_db”).
The result will be:
Now I will make few changes in the database in order to move from our naming convention and produce unwanted behavior:
● Remove the “id” attribute from the “user_has_role” table.
● Define PK in the “user_has_role” table as combination of attributes: “user_id”,
“role_id” and “role_start” .
● Remove the SQL primary key constraint from the “id” attribute in the “role” dictionary.
● Remove all foreign keys constraints.
The schema now looks like this:
Calling the procedure now returns the following result:
In the “role” table, the SQL primary key is automatically replaced by the UNIQUE constraint that is set on the attribute “role_name”. The name of the attribute is not “id” which results with the first error, although the PK has only one attribute so the second control passed. Still, “is_ok” returns an ERROR because it must do so in case any of the other two controls (“has_id”, “PK_attributes”) return an ERROR.
For the “user” table, the situation would turn out fine in case the attribute would be named “id” and not “od”. Both controls on the “user_had_role” table returned an ERROR, due to 2 reasons:
1. Naming: none of the attributes forming the SQL primary key are named “id”.
2. The PK is composed of 3 attributes instead of only 1.
So, should you automate it?
Today we’ve discussed how to implement controls on the PKs in your database, it may seem simple and basic but this is something you want to automate. After completing the database design, one push of a button could do the entire procedure on all of your databases.