On 12/21/19 1:41 PM, Boylan, Ross wrote:
My mail interface (Outlook on the Web) really can't quote properly, so I'll just do snips.
"When you create a new table in Datasheet view, Access automatically
creates a primary key for you and assigns it a field name of "ID" and
the AutoNumber data type."
That quote, and the documentation mentioned before it, is not directly relevant, since I'm not creating new tables in Access
My guess is the migration process missed that aliquotid was the PK.
Substantively aliquotid is the primary key, but in terms of formal table properties there is no primary key. The export process does create primary keys in PG for tables that have them in Access. My export process also has no foreign key relations.
It is quite likely that subjecting the current data to "should be there" constraints on primary and foreign keys will reveal features of the data that shouldn't be there, such as missing or lost references or values.
Also, if I impose those constraints when I create the table definitions, the import will become much more order sensitive, I think. If I import a table with a foreign key in a table not yet imported, it will presumably fail. And since tables can refer to each other, there may be no import order that will work. So I guess I better add foreign key constraints after the main import. Of course, that will require valid data, but that's a separate problem.
I migrated by using a slightly modified version of
' exportSQL version 3.2-dev
' www.rot13.org/~dpavlin/projects.html#sql
'
' based on exportSQL version 2.0 from www.cynergi.net/prod/exportsql/
'
' (C) 1997-98 CYNERGI - www.cynergi.net, info@xxxxxxxxxxx
' (C) Pedro Freire - pedro.freire@xxxxxxxxxxx (do not add to mailing lists without permission)
' (c) 2000-2001 Dobrica Pavlinusic <dpavlin@xxxxxxxxx> - added PostgreSQL support
It needed some tweaks to work with current PG. It does preserve primary key values.
Thanks for the references on log_cnt and sequences. I can see that just using the defaults is the easiest path, but I clearly can't do that on import. Cleaning the sequence up after import seems straightforward, though the export code isn't doing it. Whether the main application relies strictly on defaults I don't know.
This might be easier to figure out if you outline what is going on:
1) The purpose of the migration?
2) A general sense of what the application is and what it does.
3) Have you looked at the Relations tab in Access to see what if any
relationships are there?
Ross
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
