How can I verify that two relational databases are identical regardless of primary keys?

Question

I have a relational database with approximately 100 tables. Each table has a unique, numerical, primary key with synthetic values, and there are many foreign keys that link the tables. Tables are not large (tens or hundreds or records). This is a SQLite database.

For testing purposes, I need to compare two copies of a database using a linux script (simple bash scripts, perl, diff, sed are available). I need to verify that the number of records in both databases is the same and that the records have the same content and reset the differences. The problem is that the key values ​​can be different, because the relations are the same.

For instance:

There is a country table with the primary key ix_country and name, and a client table with the fields name, primary key ix_customer and foreign key ix_country.

These two databases are equal: the first database:

country: name = "USA" ix_country = 1; client: name = "Joe" ix_customer = 10 ix_country = 1

second database:

country: name = "USA" ix_country = 1771; client: name = "Joe" ix_customer = 27 ix_country = 1771

Both copies have the same structure.

Is there an easy way to do this?

Update:

Another requirement - the script must be resistant to changes in the structure. It should work if a table or field is added or deleted.

Update 2:

. SQL, " ". ( " " ) " " - , . id (, contry ). , (, , ).

SQL script , .

a perl script .

.

Answer 1

. SQL, " ". ( " " ) " " - , . id (, contry ). , (, , ).

SQL script , .

a perl script .

.

Answer 2

. .

Answer 3

, , , , diff, .

.

sqlite3 test.db 'CREATE TABLE Country (id  integer, name varchar(20))'
sqlite3 test.db 'CREATE TABLE Customer (id  integer, name varchar(20), country integer)'
sqlite3 test.db 'insert into country values (1, "USA")'
sqlite3 test.db 'insert into country values (2, "Belgium")'
sqlite3 test.db 'insert into customer values (1, "Joe", 1)'
sqlite3 test.db 'insert into customer values (1, "Peter", 2)'

sqlite3 test.db 'select cust.name, c.name from customer cust, country c where cust.country = c.id order by c.name, cust.name'

Peter|Belgium
Joe|USA

sqlite3 test.db 'select cust.name, c.name from customer cust, country c where cust.country = c.id order by c.name, cust.name' >db1.txt

bash script, db 2 , .

, , .