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

[Rahkonen Jukka (MML)]

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://sqlite.org/foreignkeys.html. 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://gdal.org/drivers/vector/sqlite.html 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://lists.osgeo.org/mailman/listinfo/gdal-dev