I am working on an application with ~ 1000 tables in a sql server database. I am having a recurring issue with a sql script.
The script is used by the consulting team to "clean" some part of the database and sometimes it will fail because a new table with a foreign key constraint was added a few month ago and until this moment no one ran the script with any data in this new table. A foreign key violation is raised and everyone yells at the devs for their crappy tools.
There is a CI test on this script, but no ones updates the data provided in the test. So the test never fails because the new tables are left empty, but the script still fails in real environments.
The possible solution
I would like to make sure that every table in the database contains at least a few rows when the script CI test is run. I could try to maintain a manual script to fill up the data and add a test that checks that every table contains some rows but I fear that it will be a maintenance nightmare.
Another solution is to have another powershell/sql that script analyses the schema and creates rows according to the column types with constant/random values in all tables ordered using a topological ordering based on the foreign keys relation graph. The script would first fill table without relations, then tables with relations to the already filled tables, etc
I have two questions:
- does this solution seems reasonable?
- am I overcomplicating things? is there simpler way to make sure a sql script won't fail because of foreign key constraint violations?
I checked existing tools but the static analysis tools don't seem to have the required features and the data generation tools are used to specify "realistic" data which I don't care about.