Re: Semi-unable to add new records to table--primary key needed?

> From: Adrian Klaver <adrian.klaver@aklaver.com>
 > Sent: Sunday, December 22, 2019 10:35 AM
.....
> Alright this is the part where I got confused. I think what is going on is:

>1) The immediate change is going to be to Access 2016 on Windows 10 64
keeping the data in Access files(.accdb)

> 2) The long term plan is to move the data from the Access files to a
stand alone SQL server, presumably Postgres. You are looking for
assistance in converting Access tables and relationships to Postgres.

> 3) At some point the GUI will point to the data in the server instead of
in the Access files.

> Is any of the above correct?

Basically, yes.
1) The current file is in the older Access .mdb format, and will stay that way for the immediate conversion to Win
10/Access2016. 
2) I'd like to go to Postgres medium term.  I have converted the Access data to Postgres via an Access/VBA program that
writesout SQL files.  It clearly needs some work. 
I think I may have everything I need to tweak it.
3) Basically yes, though in detail the GUI and the code behind it directly use "linked tables"  that are local to the
frontend.  These linked tables are the Access equivalent of symlinks to a particular table on a particular database.
Theywill use the PG ODBC driver to do the talking.  The app uses those linked tables now, but the link simply points to
thebackend file on the disk. 

One other issue came up in testing: searches against the Access backend are case insensitive, while searches against
thePG backend are case-sensitive.  It would be nice to make them case-insensitive, and I've been reading about PG
collationoptions to do so. 

Case-sensitivity is completely distinct from all the other issues I've discussed.  The sense was also that the
problem/differencewas not a show-stopper.  Current behavior is that if you search for id abc123 it will retrieve an id
storedas ABC123.  With the PG backend, searching on  abc123 retrieves nothing. 

> More below.

>>> 3) Have you looked at the Relations tab in Access to see what if any relationships are there?
....
>
>> Yes, but the export program doesn't :)  The relations tab documents many, but not all, of the relations in the
database. The relations are also a little tricky because sometimes the lack of a relation should not be considered
disqualifyingfor a specimen.  Simple example: freezer type is an id to be looked up in a small table of freezer type
idsand their names.  If the freezer type is missing or nonsense, we may still want the sample.  That can be expressed
asa left join; the "Access SQL is not SQL" problems centered on left joins. 

>Postgres is going to be stricter about this. Access has the concept of
suggested relationships that are not enforced:


>https://urldefense.proofpoint.com/v2/url?u=https-3A__support.office.com_en-2Dus_article_Guide-2Dto-2Dtable-2Drelationships-2D30446197-2D4fbe-2D457b-2Db992-2D2f6fb812b58f&d=DwIDaQ&c=iORugZls2LlYyCAZRB3XLg&r=nh70E5-mX2XsDe5lrDDMt_ZRqqGMcdTmTrRLvNmttYA&m=qa1d513KOtyWtV0u2K81rduJ0TiRaucF3gbnjVTESkc&s=F10pVnItttte6dDN-4R4t13xDrJr8zAoIp63mrYNMh0&e=

Thanks for the pointer.  I'm not sure if we're using any of those suggested relationships, but we're definitely using
relationshipsthat one infers only by looking at the SQL code.  In other words, queries link tables by fields that are
notmentioned on the relationships tab. 

......

> Postgres Foreign Key relationships either exist or they don't. Now you
can employee 'cheats' with you own triggers, but that is another subject.

>As to your freezer example:

>create table freezer_info(freezer_id int PRIMARY KEY);
>create table sample_tbl(sample_id serial PRIMARY KEY, freezer_fk int
>REFERENCES freezer_info ON UPDATE CASCADE);

The direct translation of our current app would be to omit the foreign key relation in the table definition, but to
havequeries that include a left join from sample_tbl to freezer_info. 

That raises one other question: what is the relation between the relations expressed on the server via FOREIGN KEY
declarationsand relations known to Access?  At first blush, they are completely different.  In Access a relation is an
objectthat lives on the front-end, while the server relations obviously live on the server.  I don't know if Access,
perhapsvia the ODBC spec, does anything to guarantee their consistency.  Clearly since Access relations can have
"suggestedrelations" which have no backend equivalent, the 2 sets of relations cannot in general be mirror images. 

Identifying and enforcing all foreign key relations, is something I'm planning to defer til after the migration to PG.
Thereis a noticeable amount of funky stuff in the data, which could use some housekeeping regardless of platform.  The
cleanupis not likely to be simple. 

One of the long-run benefits of using a database server is that it should limit the possibilities for funkiness by
enforcingreferential integrity and properly cleaning up after incomplete operations, instead of leaving pieces of them
inthe database.  But we can't enforce referential integrity until our  data exhibit it! And the transactional integrity
probablydepends more on proper coding on the front-end app than the choice of backend; file-based Access backends do
supporttransactions. 

Ross