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-boun...@lists.osgeo.org> Puolesta DUTRIEUX Loic
Lähetetty: torstai 3. maaliskuuta 2022 13.56
Vastaanottaja: gdal-dev@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@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Reply via email to