Create primary and foreign key on Spatialite

Question

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?

Answer 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.