How to test an SQL database [closed]

Question

For university I have to design a database for a fictional company selling oldtimer cars. Every week, there is a new sheet of tasks to accomplish. These are changes to the buisiness rules and/or new requirements. That means we will have database evolution scripts which build the database (including test data) sheet by sheet, so each new sheet is a new version (evolution).

One of the reqirements is to do proper tests (in SQL). I am quite familiar with unit testing in e.g. JUnit, but I never did anything like that. For starters, the tables have primary and foreign keys, NOT NULL and other constraints.

How do I test

• the integrity of the data (
• the key constraints (primary and foreign)
• NOT NULL constraints
• CHECK constraints
• etc.

using SQL and maybe some helpful tool? Preferably, I want to run the tests automatically.

As previously mentioned the database will be filled with dummy data.

To clarifiy the question: My teammates just know some SQL, nothing more. So it is impossible to use something like JUnit. Is there "something" simple to use, preferably in SQL or some batch executing SQL "thing" which

• does run the database evolution scripts
• asserts success/failure of queries
• does automatically wrap modifications used for a single test in a transaction which will be rolled back instead of committed in the end and sees if the transaction/rolback was performed without errors?

If not, what would be a simple, easy-to-use approach to the problem?

Answer 1

Use the same unit-test framework you'd use to test your application code. If you're not using one, just write queries (the tests are all queries) and read the results. In each test, run queries against your database to whatever you'd want to test.

• To test the schema itself (e.g. whether a given column has a foreign key), run queries against the database/tablespace/whatever that describes the schema -- information_schema in MySQL, the Oracle data dictionary views, or whatever is appropriate for the database you're using -- that select the rows that should be there, and expect that they return one row each. For example, if you have tables of users and paychecks and want to test that paychecks are foreign keyed to users, in MySQL,

  use information_schema;
  select count(*)
  from key_column_usage
  where table_schema = 'your_database' and
    table_name = 'paychecks' and
    column_name = 'user_id' and
    referenced_table_name = 'users' and
    referenced_column_name = 'id';
  

• To test the data, run queries that count deviations from what you expect to be in the database and expect they they return no rows. For example, if you have tables of users, paychecks and tax_payments, and you expect a user with no paychecks to never have tax_payments,

  select count(*)
  from users u
  where exists (select 1 from tax_payments tp where u.id = tp.id) and
    not exists (select 1 from paychecks p where u.id = p.id);
  

If you know that your schema prevents a error from being possible (through not-null constraints or foreign keys or what have you), you don't need to check the data for that error. On the other hand this is university so maybe you'll be asked to do both.