How to Test SQL Primary Key Constraints

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:

Each table should include a defined primary key.
● A SQL primary key, should be a single attribute, preferably of integer data type with AUTO_INCREMENT property set. 
Use a logical name for a SQL primary key (I’m simply using “id”).
We’ll work with a simple database model I’ve created especially for this purpose: 
How to Test SQL Primary Key Constraints: Database Model ExampleIt’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:
Does each table have “id” attribute that is part of the SQL primary key? 
Does each table have SQL primary key composed of only one attribute? 

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:

How to Test SQL Primary Key Constraints: SQL Table Example 



Now I will make few changes in the database in order to move from our naming convention and produce unwanted behavior:

● Change the name of the attribute in the “user” table to “od”.
● 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:

How to Test SQL Primary Key Constraints

Calling the procedure now returns the following result: 

How to Test SQL Primary Key Constraints: Calling Procedure 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.

-You’re welcome to ask me anything in the comments and stay tuned, as in the next article I’ll discuss possible controls over FK attributes in your database!