Database consistency check after SET REFERENTIAL_INTEGRITY FALSE;

[Bogdan Vasile]

Hi,

I have a use case where I have to disable referential integrity when populating the database.

Still, I would like to check the database consistency after all the inserts are finished.

Is there a tool/script that I could use to check that all foreign keys are correctly populated?

Thank you,

Bogdan

[Evgenij Ryazanov]

Hello.

Check of existing data on individual table can be enforced with ALTER TABLE tableName SET REFERENTIAL_INTEGRITY TRUE CHECK.

Unfortunately, there is no built-in command to force such check on all tables at once.

[Bogdan Vasile]

Thank you so much for your help! It worked!!!

I created a script that exports all the tables from my schema, and based on that, I created a script that runs the ALTER TABLE commands for all the exported tables.

All the best,

Bogdan

PS: For anyone else who needs a few more details:

The sql to export my tables is :

CALL CSVWRITE('./schema/public_tables.csv', 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=''PUBLIC''','writeColumnHeader=false fieldDelimiter=');

The bash script that creates the check script is:

tables_csv_file=./schema/public_tables.csv

check_consistency_sql_file=./schema/check_consistency.sql

echo Starting to write check_consistency_sql_file=$check_consistency_sql_file

rm "$check_consistency_sql_file"

while read table_name; do

  echo "ALTER TABLE $table_name SET REFERENTIAL_INTEGRITY TRUE CHECK;">>$check_consistency_sql_file

done <$tables_csv_file

echo Finished writing check_consistency_sql_file

PPS: I think it would have been better to generate the sql check script file directly with the CSVWRITE command, but I didn't want to spend more time on this.