[gdal-dev] Implications of "Multi-column primary key in [table] to supported" warning

[DUTRIEUX Loic]

Thanks for the many insights Jukka, it has helped me a lot in thinking where to dig and what to look at.

First, and this is what matters the most to me, I was able to confirm data integrity by comparing for each table the pg version to the sqlite version. Some types are altered (e.g. Dates which do not exist in sqlite and therefore became strings, and a JSON which also became a string, even though sqlite has a json type) but that's not a big issue.
I confirm that Foreign keys constraint are gone, if I run a "spatialite mydb.sqlite .schema | grep FOREI", only the spatialite extension tables are left. As you said it implies that the safety net is gone and the database no longer prevents me from making edits I shouldn't do but since this database is intended mainly for read only usage and not data management, that is perfectly acceptable.
As for the association tables, you guessed right, the composite key is gone and an additional ogc_fid primary key is added.

Thanks again,
Kind regards,
Loïc
________________________________________
From: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi>
Sent: 03 March 2022 13:55:33
To: DUTRIEUX Loic (JRC-ISPRA); gdal-dev at lists.osgeo.org
Subject: Re: Implications of "Multi-column primary key in [table] to supported" warning

Hi,

If you have a table with multi-column primary key (composite key) then the target table in Spatialite obviously will not have the same PK. I would guess that none of the columns in the composite is unique and usable as PK so the only possibility is to create a new unique column for the PK. Therefore the tables that you will have are not identical with the source tables by their schemas. The foreign key constraints between the tables are dropped any way, ogr2ogr does not try to transfer them ever.

I think you can answer your question yourself by making a test with your own data. If all of the tables that have a composite PK get a new PK field in Spatialite (I guess it will of type "integer autoincrement") and if the row counts in source and target tables match I believe that the data has been transferred right. When it comes to question "is it safe to assume that db relations won't get mixed up" you do not have any constrained relations left in Spatialite. The data integrity is OK immediately after the conversion but at that stage there is nothing in Spatialite that prevents you from making edits that you should't do. But you can add similar constraints that you have in PostGIS afterward into SQLite https://urldefense.com/v3/__https://sqlite.org/foreignkeys.html__;!!DOxrgLBm!XPxXzkROhjk7yXj_UMlx2cbkKXEZfzDfmaMiH6Vp7pfUEbQ4fUUGEDTCfGCvSCFs8-qDs1E$ . I am not sure that all type of constraints that you can have in PostGIS are supported in SQLite but foreign key should behave in the same way.

SQLite driver https://urldefense.com/v3/__https://gdal.org/drivers/vector/sqlite.html__;!!DOxrgLBm!XPxXzkROhjk7yXj_UMlx2cbkKXEZfzDfmaMiH6Vp7pfUEbQ4fUUGEDTCfGCvSCFskfue4zU$  supports prelude_statements but if you want to re-create constraints you would need something like postlude_statements. It means that ogr2ogr cannot do the whole job but you must run the required SQL statements with ogrinfo or some SQLite client.

-Jukka Rahkonen-



-----Alkuperäinen viesti-----
Lähettäjä: gdal-dev <gdal-dev-bounces at lists.osgeo.org> Puolesta DUTRIEUX Loic
Lähetetty: torstai 3. maaliskuuta 2022 13.56
Vastaanottaja: gdal-dev at lists.osgeo.org
Aihe: [gdal-dev] Implications of "Multi-column primary key in [table] to supported" warning

Hi everyone,

Quick question, I'm using ogr2ogr to clone a postGIS database to a spatialite file database (by the way, very convenient that ogr2ogr handles the transfer of non-spatial tables too). The database contains many-to-many relations and as a consequence association tables with multiple primary keys. When I run ogr2ogr I get a warning saying that Multi-column primary key are not supported.
Should I be worried of that warning for my use case or is it safe to assume that db relations won't get mixed up.

Thanks and kind regards,
Loïc
_______________________________________________
gdal-dev mailing list
gdal-dev at lists.osgeo.org
https://urldefense.com/v3/__https://lists.osgeo.org/mailman/listinfo/gdal-dev__;!!DOxrgLBm!XPxXzkROhjk7yXj_UMlx2cbkKXEZfzDfmaMiH6Vp7pfUEbQ4fUUGEDTCfGCvSCFsH0SPbLQ$