Lost Foreign key during conversion of SQLite to PostgreSQL using ogr2ogr

Question

I want to centralise several small sqlite databases into a single, big, postgresql database. I am using ogr2ogr to convert my sqlite / spatialite (v3.7.17 / v4.1.1) databases, each one consisting of one geometry table and several attribute tables (linked to the geometry table via foreign keys), to the postgresql / postgis database ( v9.3 / v2.1.3). Here is the code:

ogr2ogr -append -lco LAUNDER=no -lco SCHEMA=public -lco GEOMETRY_NAME=geom -f PostgreSQL PG:"..." -a_srs EPSG:4269 c:\Mydata1.sqlite line attrib1 attrib2

All my int4 primary keys (ex: line_pk, attrib1_pk, attrib2_pk) are converted to serial type and their names are all changed to the same name: ogc_fid (eg I have ogc_fid as PK in my line, attrib1 and attrib2 tables). I can live with this, but I find that all my foreign key constraints (from the sqlite database) are eliminated during the conversion process! Any idea why this is so and how to correct this? I can always re-code the constraints manually, but then I will have to somehow manage what value is put into my foreign keys each time I append a new set of data...

Answer 1

I do not believe that GDAL cares anything about the foreign key constraints and relational model. It has just made to handle one layer at a time. I would consider to copy your int4 primary keys into normal attributes and take care on the Spatialite side that they are unique over all your distinct databases. Once source data is in order you can append all data into PostGIS tables and create unique indexes and foreign key constraints. When appending even more data you must check the max ID value from PostGIS and update new IDs to start from (max+1).

You can define the name of the primary key that GDAL creates with layer creation option -lco FID=xxx as documented in http://www.gdal.org/drv_pg.html but that does not solve your real problem.