I don't know if I have "a lot" of tables without a PK, I just know I have more tables than I like where no automatic comparison key was selected (and by the way, if I create a custom comparison key, does that get saved with the project?).
What I meant with "quick way of setting all columns" is something like this: I have a list of 500 tables on the existing Tables & Views tab, and let's say 50 of those don't have a comparison key. I want to be able to press a button, it shows me the 50 "problem" tables in a dialog (checked listbox, one item per table, checked by default). In there, I have a button that says "Match on all columns" (and possibly something fancier, see below). If I click on it, it will generate comparison-keys for all checked tables, with those comparison keys including ALL columns of the table. Alternatively, have a way of making it pick a table's unique key as the comparison key for all tables in that dialog.
As for the comparison key that is picked by default: I'm not sure I agree that the PK is always (or even often) the right one, there's plenty of people that use a single numeric column as the physical primary key, whereas the logical primary key is usually much wider (and expressed as a unique key, usually the widest unique key on the table).
In the Table & Views tab, the first column (Comparison Key) shows the name of the comparison key (defaulted to PK) but when I drop down on that column in the grid, it shows me all sorts of columns of that table in the popped up grid even when I don't have Custom selected. It's not really clear why it show columns that are not in the selected non-custom key.
Also... Can we have an option to exclude temporary tables from the Table & Views display? I don't see how those could have data in them (unless Data Compare also compares structures).
Also, can we have an option to exclude SYS_IOT tables from the Table & Views display?
Also... When it picks PKs as the default comparison key, does it do so based on the columns in the PK, or just the fact that they are the primary key? Or that they have the same name?
I also find the name "Comparison Key" confusing, certainly when I look at the rightmost grid column called "Columns in Comparison". My understanding is that the comparison key is used to find matching rows, whereas Columns In Comparison are the columns that are actually compared. If that understanding is correct, shouldn't "Comparison Key" be "Matching Key" ?
What happens if there is more than one row in one database matching a single row in another database?
Once I have everything configured (my keys, my options, my list of tables to compare)... I need to be able to save that as part of my project, so if I open the project again I don't have to configure everything again.
Sorry for mixing everything into a single topic
Tom Harris wrote:Hi Johannes,
good question. You are correct that if there is no primary key or unique constraint then no default will be set. We need some unique way of matching up records. I'm not sure I follow your request for quickly setting all columns - how would this work?
Do you have lots of tables that do not have a primary key, or unique constraint. If so, is there an obvious 'index' that could be used? If so use this as a custom way of comparing records.
Do reply if I'm not getting your point?
Kind regards, Tom