1

I try to create a database on spatialite (for various reason, I can't use PostGis). I need to link a table to another one using a foreign key. The problem with spatialite is that it is not possible to define a primary key afterwards, so I create a new table (tMAJ_TBE_REB_15₂) with the Primary key « C_SGE_15 » (it is field with no null value, and unique values) and then I try to copy the content from the other one (which have got the default Primary Key PUIK).

When I start this command, I get this error message.

Une erreur est apparue lors de l'exécution du code Python :
Traceback (most recent call last):
  File "C:/Documents and Settings/XXXXXXX/.qgis2/python/plugins\QspatiaLite\QspatiaLiteApp.py", line 178, in runQuery
    header,data=self.db.executeQuery(query)
  File "C:/Documents and Settings/XXXXXXX/.qgis2/python/plugins\QspatiaLite\Classes\Database.py", line 51, in executeQuery
    cursor.execute(query,params)
IntegrityError: column C_SGE_15 is not unique

Version de Python :
2.7.4 (default, Apr  6 2013, 19:54:46) [MSC v.1500 32 bit (Intel)]
Version de QGIS :
2.8.2-Wien Wien, 1b929ef
Chemin vers Python : ['C:/Documents and Settings/XXXXXXX/.qgis2/python/plugins\\processing', 'C:/PROGRA~1/QGISWI~1/apps/qgis/./python', u'C:/Documents and Settings/XXXXXXX/.qgis2/python', u'C:/Documents and Settings/XXXXXXX/.qgis2/python/plugins', 'C:/PROGRA~1/QGISWI~1/apps/qgis/./python/plugins', 'C:\\PROGRA~1\\QGISWI~1\\bin\\python27.zip', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\DLLs', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\plat-win', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\lib-tk', 'C:\\PROGRA~1\\QGISWI~1\\bin', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\PIL', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\jinja2-2.7.2-py2.7.egg', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\markupsafe-0.23-py2.7-win32.egg', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\python_dateutil-2.2-py2.7.egg', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\pytz-2014.2-py2.7.egg', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\win32', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\win32\\lib', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\Pythonwin', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\Shapely-1.2.18-py2.7-win32.egg', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\six-1.6.1-py2.7.egg', 'C:\\PROGRA~1\\QGISWI~1\\apps\\Python27\\lib\\site-packages\\wx-2.8-msw-unicode', u'C:/Documents and Settings/XXXXXXX/.qgis2//python', 'C:\\Documents and Settings\\XXXXXXX\\.qgis2\\python\\plugins\\DigitizingTools\\tools', 'C:\\Documents and Settings\\XXXXXXX\\.qgis2\\python\\plugins\\mmqgis/forms', 'C:\\PROGRA~1\\QGISWI~1\\apps\\qgis\\python\\plugins\\fTools\\tools', 'C:/XXX/MAJ_TBE_REB']

To resume my idea, I would like to have a geographic table link using the field C_SGE_15 to another table tMAJ_TBE_REB_15₂ which contain description, etc. The goal after is to create a form easy to use. I do this on Qspatialite and Qgis 2.8.2.

CREATE TABLE 'tMAJ_TBE_REB_15_2' (
'C_SGE_09' TEXT,
'MODIF_15' INTEGER,
'TypModif15' TEXT,
'C_SGE_15' TEXT NOT NULL PRIMARY KEY,
'NOM_09' TEXT,
'NOM_15' TEXT,
'TBE_09'  INTEGER,
'TBE_15'  INTEGER,
'REB_09' INTEGER,
'REB_15' INTEGER,
'Espec_pres' TEXT,
'Obs_2009' TEXT,
'Obs_2015' TEXT)

INSERT INTO   "tMAJ_TBE_REB_15_1" (
'C_SGE_09',
'MODIF_15',
'TypModif15',
'C_SGE_15',
'NOM_09',
'NOM_15',
'TBE_09',
'TBE_15',
'REB_09',
'REB_15',
'Espec_pres',
'Obs_2009',
'Obs_2015')
SELECT 'C_SGE_09',
'MODIF_15',
'TypModif15',
'C_SGE_15',
'NOM_09',
'NOM_15',
'TBE_09',
'TBE_15',
'REB_09',
'REB_15',
'Espec_pres',
'Obs_2009',
'Obs_2015',
FROM  "Modifs_MAJ_TBE_REB_15" 

Does anybody see from where the error come from?

1

Are you sure that there are no duplicate C_SGE_15 values? You can check with:

SELECT Count(*) FROM "Modifs_MAJ_TBE_REB_15" AS t1 
JOIN "Modifs_MAJ_TBE_REB_15" AS t2 
ON t1.C_SGE_15 = t2.C_SGE_15
WHERE t1.ROWID < t2.ROWID;

If you get a count > 0 then you have multiple rows with the same C_SGE_15.

  • Hi Micha, I tried it and the result is 0. I think there is a problem somewhere because, I have done the same on postgis, and it work without any problem... – Onesime Jul 23 '15 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.