Why am I getting a SQLite «foreign key mismatch» error when executing script below?
DELETE
FROM rlsconfig
WHERE importer_config_id=2 and
program_mode_config_id=1
Here is main table definition:
CREATE TABLE [RLSConfig] (
"rlsconfig_id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
"importer_config_id" integer NOT NULL,
"program_mode_config_id" integer NOT NULL,
"l2_channel_config_id" integer NOT NULL,
"rls_fixed_width" integer NOT NULL
,
FOREIGN KEY ([importer_config_id])
REFERENCES [ImporterConfig]([importer_config_id]),
FOREIGN KEY ([program_mode_config_id])
REFERENCES [ImporterConfig]([importer_config_id]),
FOREIGN KEY ([importer_config_id])
REFERENCES [ImporterConfig]([program_mode_config_id]),
FOREIGN KEY ([program_mode_config_id])
REFERENCES [ImporterConfig]([program_mode_config_id])
)
and referenced table:
CREATE TABLE [ImporterConfig] (
"importer_config_id" integer NOT NULL,
"program_mode_config_id" integer NOT NULL,
"selected" integer NOT NULL DEFAULT 0,
"combined_config_id" integer NOT NULL,
"description" varchar(50) NOT NULL COLLATE NOCASE,
"date_created" datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP),
PRIMARY KEY ([program_mode_config_id], [importer_config_id])
,
FOREIGN KEY ([program_mode_config_id])
REFERENCES [ProgramModeConfig]([program_mode_config_id])
)
I have the following 5 tables defined with a few records inserted into the 1st 4. This is using sqlite 3.7.1.7 with foreign key constaint enabled.
create table if not exists subject (id varchar(50) primary key,desc varchar(100));
insert into subject (id,desc) values ("subject1","test subject");
create table if not exists subjectlevel (id_subject_id varchar(50) references subject(id) on delete cascade, id integer not null, desc varchar(100) not null, questmcmaxselections integer not null, primary key (id_subject_id,id));
insert into subjectlevel (id_subject_id,id,desc,questmcmaxselections) values ("subject1",1,"test subject1 level 1",4);
insert into subjectlevel (id_subject_id,id,desc,questmcmaxselections) values ("subject1",2,"test subject1 level 2",4);
create table if not exists questmc (id integer primary key, text varchar(300) not null, includeallanswers int not null, subject_id varchar(50), subjectlevel_id integer, foreign key (subject_id, subjectlevel_id) references subjectlevel (id_subject_id,id) on delete cascade);
insert into questmc (text,includeallanswers,subject_id,subjectlevel_id) values ("this is a _ question", 1, "subject1",1);
create table if not exists questmcselection (id integer primary key, text varchar(100) not null, subject_id varchar(50), subjectlevel_id integer, foreign key (subject_id, subjectlevel_id) references subjectlevel (id_subject_id,id) on delete cascade);
insert into questmcselection (text,subject_id,subjectlevel_id) values ("this is a solution","subject1",1);
create table if not exists questmc_questmcselection(id integer primary key, answer integer not null, questmc_id integer, questmcselection_id integer, subject_id varchar(50), subjectlevel_id integer, foreign key (questmc_id) references questmc(id) on delete cascade, foreign key (questmcselection_id) references questmcselection (id) on delete cascade, foreign key (subject_id,subjectlevel_id) references questmc (subject_id,subjectlevel_id) on delete cascade, foreign key (subject_id,subjectlevel_id) references questmcselection (subject_id,subjectlevel_id));
if i attempt to delete the second record in the subjectlevel table, i get a foreign key mismatch error as long as table questmc_questmcselection is defined.
sqlite> delete from subjectlevel where id=2;
Error: foreign key mismatch - "questmc_questmcselection" referencing "questmcselection"
questmc, questmcselection, and questmc_questmcselection have no related existing records that should prevent this deletion. Any idea why this error occurs?
I was searching for a way to highlight zones (regions, provinces, counties, etc) on a map, and I don’t need super precise maps so I wrote this script, based on picking up black and white maps (2 colors BW .png or .gif tested) and filling them with colors, writing down a sqlite database to associate zones with names (and other data as well), and reuse the map and the DB to display data, in my example reading a simple .txt file.
It’s all based on this thread and this other thread.
So I have two modes:
The Map «creation mode» : you provide a map image and you start to pick up colors, set «upper level» region/state, and by clicking on a region you fill it and you name it, and all the data are saved on a sqlite DB (auto-created)
when you have the map image and a DB with the correct associations, you can switch the «mode» to «show» (as by .ini file) and the script tries to read a «datafile» showing the zone names listed in datafile.
The code:
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=IconemapFlooder.ico
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
;MAP Flooder
;(C) NSC 2021
#include <GUIConstants.au3>
#include <_GOLLOG.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Misc.au3>
#include <GDIPlus.au3>
Opt(«mousecoordmode», 2)
Global $prgname = «MAP Flooder», $ver = «V.0.7», $Buttoncolor = «0xFF00FF», $MPini = @ScriptDir & «MapFlooder.ini», $btest
Global $dbfullpath, $dbtable, $dbFields, $mapfile, $FloodMode, $datafile
Global $HDC, $hBrush, $hGraphics, $obj_orig
Global $Pic1, $gui, $width, $height, $bColor, $realtimeCoords, $lastclickcoords, $inputSup, $zonecountNum,$labeltest
#Region program
Gollog(«>>>>>> Start MAP Flooder » & $ver)
ctrlini()
Gui()
SQLiteDBcreate()
If $FloodMode = «createdb» Then
Gollog(«CreateDB Mode»)
DBFlooder()
Else
Gollog(«Show MAP mode»)
MapShow(«show»)
EndIf
Close()
#EndRegion program
#Region funcS
Func Gui()
_GDIPlus_Startup()
$Pic1 = _GDIPlus_BitmapCreateFromFile($mapfile)
$width = _GDIPlus_ImageGetWidth($Pic1)
$height = _GDIPlus_ImageGetHeight($Pic1)
If $FloodMode = «createdb» Then
$gui = GUICreate($prgname & » » & $ver, $width + 150, $height)
$labelLoadedMap = GUICtrlCreateLabel(«Loaded Map», $width + 10, 5)
$labelLoadedMap2 = GUICtrlCreateLabel(_FileToFileName($mapfile), $width + 10, 25)
$labeldim = GUICtrlCreateLabel(«Width*Height», $width + 10, 45)
$labeldim2 = GUICtrlCreateLabel($width & » * » & $height, $width + 10, 65)
$lastclickcoordslabel = GUICtrlCreateLabel(«Last Click Coords», $width + 10, 100)
$lastclickcoords = GUICtrlCreateLabel(«xx — xx», $width + 10, 120, 180, 20)
$realtimeCoordslabel = GUICtrlCreateLabel(«Real Time Coords», $width + 10, 140)
$realtimeCoords = GUICtrlCreateLabel(«Real Time Coords», $width + 10, 160, 80, 20)
$SuPzonelabel = GUICtrlCreateLabel(«Supzone (region-state)», $width + 10, 200)
$inputSup = GUICtrlCreateInput(«sup», $width + 10, 220, 80, 20)
$bColor = GUICtrlCreateButton($Buttoncolor, $width + 10, 250, 130, 30)
GUICtrlSetBkColor($bColor, $Buttoncolor)
$zonecountlabel = GUICtrlCreateLabel(«Done Zone Count:», $width + 10, 320, 100, 20)
$zonecountNum = GUICtrlCreateLabel(«x», $width + 10, 340, 100, 20)
$btest = GUICtrlCreateButton(«TEST MAP», $width + 10, 380, 130, 30)
$labeltest = GUICtrlCreateLabel(«», $width + 10, 420, 130, 30)
Else
$gui = GUICreate($prgname & » » & $ver, $width, $height)
EndIf
GUISetState()
$HDC = _WinAPI_GetDC($gui)
$hGraphics = _GDIPlus_GraphicsCreateFromHDC($HDC)
_GDIPlus_GraphicsDrawImageRect($hGraphics, $Pic1, 0, 0, $width, $height)
EndFunc ;==>Gui
Func MapShow($showmode) ; reading a simple text file with zone names, searching for names in DB and fill the map using stored coordinates
If $showmode = «show» Then
Local $aLines = FileReadToArray($datafile)
Local $iLineCount = @extended
EndIf
If $showmode = «test» Then $iLineCount = 1
If @error Then
MsgBox(48, «MapFlooder», «There was an error reading the data file. @error: » & @error) ;
Gollog(«There was an error reading the data file. @error: » & @error)
Close()
Else
Gollog(«start filling zones»)
_SQLite_Startup()
_SQLite_Open($dbfullpath) ; open Database with zone definitions
Local $hQuery, $aRow
For $i = 0 To $iLineCount — 1
If $showmode = «show» Then
_SQLite_Query(-1, «SELECT * FROM » & $dbtable & » where zone = ‘» & $aLines[$i] & «‘ ORDER BY zone ASC;», $hQuery) ; the query
EndIf
If $showmode = «test» Then
_SQLite_Query(-1, «SELECT * FROM » & $dbtable & » ORDER BY zone ASC;», $hQuery) ; the query
EndIf
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
$hBrush = DllCall(«gdi32.dll», «long», «CreateSolidBrush», «int», $aRow[2]) ; fill color read from DB
$obj_orig = DllCall(«gdi32.dll», «int», «SelectObject», «int», $HDC, «int», $hBrush[0])
DllCall(«gdi32.dll», «int», «FloodFill», «int», $HDC, «int», $aRow[3], «int», $aRow[4], «int», 0x000000)
If $showmode = «test» Then
GUICtrlSetData($labeltest,$aRow[0])
Sleep(200)
GUISetState()
EndIf
WEnd
_SQLite_QueryFinalize($hQuery)
Next
_SQLite_Close()
_SQLite_Shutdown()
EndIf
While 1
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then ExitLoop
WEnd
EndFunc ;==>MapShow
Func DBFlooder()
$zonecount = 0
While 1
$mp = MouseGetPos()
GUICtrlSetData($realtimeCoords, $mp[0] & » — » & $mp[1])
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then ExitLoop
If $msg = $bColor Then colorP()
If $msg = $btest Then MapShow(«Test»)
If $mp[0] < $width And $mp[1] < $height And _IsPressed(«01″) And WinActive($gui) Then
$mp = MouseGetPos()
GUICtrlSetData($lastclickcoords, $mp[0] & » — » & $mp[1])
$hBrush = DllCall(«gdi32.dll», «long», «CreateSolidBrush», «int», $Buttoncolor) ; fill color ok
$obj_orig = DllCall(«gdi32.dll», «int», «SelectObject», «int», $HDC, «int», $hBrush[0])
DllCall(«gdi32.dll», «int», «FloodFill», «int», $HDC, «int», $mp[0], «int», $mp[1], «int», 0x000000)
Local $Zone = InputBox(«Map Floode», «Zone ?»)
If $Zone = «» Or @error = 1 Then ; when manage wrong click, possibility to repeat
; set ‘temp’ color to highlight the ‘wrong’ click
$hBrush = DllCall(«gdi32.dll», «long», «CreateSolidBrush», «int», 0x4ccfc6) ; fill color wrong
$obj_orig = DllCall(«gdi32.dll», «int», «SelectObject», «int», $HDC, «int», $hBrush[0])
DllCall(«gdi32.dll», «int», «FloodFill», «int», $HDC, «int», $mp[0], «int», $mp[1], «int», 0x000000)
; restore color
$hBrush = DllCall(«gdi32.dll», «long», «CreateSolidBrush», «int», $Buttoncolor) ; fill color ok
$obj_orig = DllCall(«gdi32.dll», «int», «SelectObject», «int», $HDC, «int», $hBrush[0])
Else
_SQLite_Startup()
_SQLite_Open($dbfullpath) ; open Database
Local $SupZone = GUICtrlRead($inputSup)
Local $data = ‘»‘ & $Zone & ‘»,»‘ & $SupZone & ‘»,»‘ & $Buttoncolor & ‘»,’ & $mp[0] & «,» & $mp[1]
_SQLite_Exec(-1, «INSERT INTO » & $dbtable & «(» & $dbFields & «) VALUES (» & $data & «);»)
If @error = -1 Then
GOLLOG(«Error insert record»)
MsgBox(48, «Error», «insert record»)
EndIf
$zonecount += 1
GUICtrlSetData($zonecountNum, $zonecount)
_SQLite_Close()
_SQLite_Shutdown()
EndIf
EndIf
WEnd
EndFunc ;==>DBFlooder
Func Close()
Gollog(«<<<<<<< closing…»)
_WinAPI_ReleaseDC($gui, $HDC)
_GDIPlus_GraphicsDispose($hGraphics)
_GDIPlus_Shutdown()
Exit
EndFunc ;==>Close
Func SQLiteDBcreate() ;complete path e filename
If Not FileExists($dbfullpath) Then
GOLLOG(«perform SQLite DB creation»)
Local $dbfolder = _FileToFilePath($dbfullpath)
;Local $dbfile = _FileToFileName($dbfullpath)
If Not FileExists($dbfolder) Then DirCreate($dbfolder)
; =====================>>>>> START SQL DLL
_SQLite_Startup()
_SQLite_Open($dbfullpath) ; open Database
; creating first table
If _SQLite_Exec(-1, «CREATE TABLE » & $dbtable & » (» & $dbFields & «);») = $SQLITE_OK Then
GOLLOG(«DB table — » & $dbtable & » — creation ok»)
Else
GOLLOG(«Error creating DB table : » & @error)
EndIf
_SQLite_Close()
_SQLite_Shutdown()
Else
Gollog(«DB already exist»)
EndIf
EndFunc ;==>SQLiteDBcreate
; #FUNCTION# ====================================================================================================================
; Name ……….: _FileToFilePath
; Description …: Returns a folder path from a FQPN (Fully Qualified Path Name)
; Syntax ……..: _FileToFilePath($sPath)
; Parameters ….: $sPath — a string value.
; Return values .: Success — String
; Failure — Empty string as returned from StringLeft()
; Author ……..: Sam Coates
; ===============================================================================================================================
Func _FileToFilePath($sPath)
Local $sReturn = StringLeft($sPath, StringInStr($sPath, «», 0, -1) — 1)
Return ($sReturn)
EndFunc ;==>_FileToFilePath
; #FUNCTION# ====================================================================================================================
; Name ……….: _FileToFileName
; Description …: Returns a filename from a FQPN (Fully Qualified Path Name)
; Syntax ……..: _FileToFileName($sPath[, $bIncludeExtension = True])
; Parameters ….: $sPath — a string value.
; $bIncludeExtension — [optional] a boolean value. Default is True.
; Return values .: Success — String
; Failure — Empty string as returned from StringLeft()
; Author ……..: Sam Coates
; ===============================================================================================================================
Func _FileToFileName($sPath, $bIncludeExtension = True)
Local $sReturn = StringTrimLeft($sPath, StringInStr($sPath, «», 0, -1))
If $bIncludeExtension = False Then $sReturn = StringLeft($sReturn, StringInStr($sReturn, «.», 0, -1) — 1)
Return ($sReturn)
EndFunc ;==>_FileToFileName
Func colorP() ; modified for BGR color
GOLLOG(«Color Picker»)
Local $color = _ChooseColor(2)
If $color = -1 Then
GOLLOG(«no color selected»)
Else
Local $sCr = Hex($color, 6)
Local $RGB_Buttoncolor = ‘0x’ & StringMid($sCr, 1, 2) & StringMid($sCr, 3, 2) & StringMid($sCr, 5, 2)
GUICtrlSetBkColor($bColor, $RGB_Buttoncolor)
; BGR color
$Buttoncolor = ‘0x’ & StringMid($sCr, 5, 2) & StringMid($sCr, 3, 2) & StringMid($sCr, 1, 2)
GUICtrlSetData($bColor, $Buttoncolor)
GOLLOG(«new color » & $Buttoncolor & » selected»)
EndIf
EndFunc ;==>colorP
Func ctrlini() ;ini read
If FileExists($MPini) Then
GOLLOG(«found: » & $MPini)
$mapfile = IniRead($MPini, «map», «mapfile», «»)
$datafile = IniRead($MPini, «map», «datafile», «»)
$dbfullpath = IniRead($MPini, «db», «dbfullpath», «»)
$dbtable = IniRead($MPini, «db», «dbtable», «»)
$dbFields = IniRead($MPini, «db», «dbfields», «»)
$FloodMode = IniRead($MPini, «mode», «mode», «»)
Else
GOLLOG($MPini & » NOT found..»)
Close()
EndIf
EndFunc ;==>ctrlini
#EndRegion funcS
All the needed files plus some example (image maps and DBs)
Link to all demo files
To test, copy all in a single folder and adjust the mapflooder.ini, also you can add to you includes the _gollog.au3 (used for log, you can avoid it deleting all Gollog() lines)
0 / 0 / 0
Регистрация: 25.02.2011
Сообщений: 5
1
24.01.2013, 10:48. Показов 2415. Ответов 2
Здравствуйте, уважаемые форумчане!
Есть три таблицы связанные между собой, ниже скрипты создания этих таблиц (кусок скрипта в lua):
Код
CreateClassificators = ""
.." CREATE TABLE IF NOT EXISTS CLASSIFICATORS ("
.." [CL_ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,"
.." [CL_NAME] TEXT, "
.." [CL_FOREIGN_OWNER] INTEGER);",
CreateTrees = ""
.." CREATE TABLE IF NOT EXISTS TREES ("
.." [TREE_ID] INTEGER NOT NULL PRIMARY KEY ON CONFLICT ROLLBACK AUTOINCREMENT CONSTRAINT [PART] UNIQUE ON CONFLICT ROLLBACK, "
.." [CL_ID] INTEGER CONSTRAINT [FK_TREES_REFERENCE_CLASSIFICATORS] REFERENCES [CLASSIFICATORS]([CL_ID]) ON DELETE CASCADE ON UPDATE CASCADE,"
.." [PARENT_TREE_ID] INTEGER, "
.." [TREE_NAME] TEXT, "
.." [DATE_OF_CREATION] DATA, "
.." [EXTERNAL_ID] INTEGER, "
.." [TREE_CHANGEABLE] INTEGER, "
.." [SIGNATURE_STAMP] INTEGER, "
.." [TYPE_ID] INTEGER, "
.." [TREE_PROPERTIES_ID] INTEGER, "
.." [CLOSED] INTEGER);",
CreateAccords = ""
.." CREATE TABLE IF NOT EXISTS ACCORDS ("
.." [AC_ID] INTEGER NOT NULL PRIMARY KEY ON CONFLICT ROLLBACK AUTOINCREMENT, "
.." [DOC_ID] INTEGER NOT NULL CONSTRAINT [FK_ACCORDS_REFERENCE_DOCUMENTS] REFERENCES [DOCUMENTS]([DOC_ID]) ON DELETE CASCADE ON UPDATE CASCADE,"
.." [TREE_ID] INTEGER NOT NULL CONSTRAINT [FK_ACCORDS_REFERENCE_TREES] REFERENCES [TREES]([TREE_ID]) ON DELETE CASCADE ON UPDATE CASCADE);",
Таблицы связаны между собой — CLASSIFICATORS — Родительская таблица
связана с дочерней TREES, через CL_ID
а TREES в свою очередь связана с дочерней ACCORD, через TREE_ID
добавляю в эти таблицы связанные элементы
удаляю элемент из CLASSIFICATOR двумя способами:
1) через выполнение запроса из скрипта Lua
DELETE FROM CLASSIFICATORS WHERE CL_ID = :CL_ID;
в резьтате удаляется элемент из таблицы CLASSIFICATOR, НО СВЯЗАННЫЕ С НИМ ЭЛЕМЕНТЫ ИЗ ДОЧЕРНИХ ТАБЛИЦ (TREES, ACCORDS)НЕ УДАЛЯЮТСЯ
2) через СУБД sqllite
в результате получаю ошибку: «foreign key mismatch»
Кто-нибудь сталкивался с этой проблемой? Подскажите, пожалуйста, решение.
Буду очень признателен…
0
Having worked with databases for over 30 years, and after looking over your database definitions, I see several issues with your database that should be corrected, restructured, or optimized. I am not going to go into all of those issues since this is primarily an AutoIt forum not a SQL and/or database forum. However, I will try to answer your specific question as to why you are getting this particular foreign key error, how you can begin to trouble shoot such issues in SQLite in the future, and one way that you can resolve this particular error.
First, to identify glaring foreign key issues, you can execute the following SQLite pragma command on the database. When you execute the command on the DB that you attached, you will see the following error. The pragma command will not find all foreign key errors, just the ones that the processor can detect at the time.
pragma foreign_key_check; Result: foreign key mismatch - "postinstall_user" referencing "postinstall"
Why are you getting that error? The following excerpt from the SQLite documentation will shed some light on the issue:
Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index. If the parent key columns have a UNIQUE index, then that index must use the collation sequences that are specified in the CREATE TABLE statement for the parent table. . . . If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys. Errors reported when content is changed are "DML errors" and errors reported when the schema is changed are "DDL errors". So, in other words, misconfigured foreign key constraints that require looking at both the child and parent are DML errors. The English language error message for foreign key DML errors is usually "foreign key mismatch" but can also be "no such table" if the parent table does not exist. Foreign key DML errors are reported if: * The parent table does not exist, or * The parent key columns named in the foreign key constraint do not exist, or * The parent key columns named in the foreign key constraint are not the primary key of the parent table and are not subject to a unique constraint using collating sequence specified in the CREATE TABLE, or * The child table references the primary key of the parent without specifying the primary key columns and the number of primary key columns in the parent do not match the number of child key columns.
In this particular case, the third bullet, above, is the reason why you are having an issue. If you refer back to the result of the pragma command, it say that there is a foreign key defined in the postinstall_user table that references the postinstall table, that has a problem. Your postinstall_user table is defined as:
CREATE TABLE [postinstall_user] ( [installer_id] INTEGER NOT NULL REFERENCES [installer]([id]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, [sequence_id] INTEGER NOT NULL REFERENCES [postinstall]([sequence_id]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, [user_id] INTEGER NOT NULL REFERENCES [user]([id]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY ( [installer_id], [sequence_id], [user_id] ) )
As you can see, there is one foreign key in that table that references the postinstall table. sequence_id is not the complete primary key in the postinstall table. The primary key in that table is defined as «PRIMARY KEY([installer_id], [sequence_id]))». Therefore to satisfy the third bullet of the documentation, if you change the definition of that foreign key to match the primary key in the postinstall table, it should fix your issue. So the definition of that postinstall_user table should look something like:
CREATE TABLE postinstall_user ( installer_id INTEGER NOT NULL, sequence_id INTEGER NOT NULL, user_id INTEGER NOT NULL, PRIMARY KEY (installer_id, sequence_id, user_id), FOREIGN KEY (installer_id, sequence_id) REFERENCES postinstall (installer_id, sequence_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED );
After making that change, when I delete the installer record or a user record, the related records, as you defined them, are successfully deleted.
I hope that helps.
Installer — New.db
Edited December 30, 2020 by TheXman
Attached modified DB
SQLite Foreign Key Support
Overview
This document describes the support for SQL foreign key constraints
introduced in SQLite version 3.6.19 (2009-10-14).
The first section introduces the
concept of an SQL foreign key by example and defines the terminology
used for the remainder of the document. Section 2 describes the steps
an application must take in order to enable foreign key constraints in
SQLite (it is disabled by default). The next section, section 3,
describes the indexes that the user must create in order to use
foreign key constraints, and those that should be created in order for
foreign key constraints to function efficiently. Section 4 describes
the advanced foreign key related features supported by SQLite and
section 5 describes the way the ALTER and DROP TABLE commands are
enhanced to support foreign key constraints. Finally, section 6
enumerates the missing features and limits of the current implementation.
This document does not contain a full description of the syntax used
to create foreign key constraints in SQLite. This may be found as
part of the documentation for the CREATE TABLE statement.
1. Introduction to Foreign Key Constraints
SQL foreign key constraints are used to enforce «exists» relationships
between tables. For example, consider a database schema created using
the following SQL commands:
CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER -- Must map to an artist.artistid! );
The applications using this database are entitled to assume that for
each row in the track table there exists a corresponding row in the
artist table. After all, the comment in the declaration says so.
Unfortunately, if a user edits the database using an external tool or
if there is a bug in an application, rows might be inserted into the
track table that do not correspond to any row in the artist
table. Or rows might be deleted from the artist table, leaving
orphaned rows in the track table that do not correspond to any of
the remaining rows in artist. This might cause the application
or applications to malfunction later on, or at least make coding the
application more difficult.
One solution is to add an SQL foreign key constraint to the database
schema to enforce the relationship between the artist and
track table. To do so, a foreign key definition may be added
by modifying the declaration of the track table to the following:
CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) );
This way, the constraint is enforced by SQLite. Attempting to insert
a row into the track table that does not correspond to any
row in the artist table will fail, as will attempting to
delete a row from the artist table when there exist dependent
rows in the track table There is one exception: if the foreign
key column in the track table is NULL, then no corresponding
entry in the artist table is required. Expressed in SQL, this
means that for every row in the track table, the following
expression evaluates to true:
trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
Tip: If the application requires a stricter relationship between
artist and track, where NULL values are not permitted
in the trackartist column, simply add the appropriate
«NOT NULL» constraint to the schema.
There are several other ways to add an equivalent foreign key declaration
to a CREATE TABLE statement. Refer to the
CREATE TABLE documentation for details.
The following SQLite command-line session illustrates the effect of the
foreign key constraint added to the track table:
sqlite> SELECT * FROM artist; artistid artistname -------- ----------------- 1 Dean Martin 2 Frank Sinatra sqlite> SELECT * FROM track; trackid trackname trackartist ------- ----------------- ----------- 11 That's Amore 1 12 Christmas Blues 1 13 My Way 2 sqlite> -- This fails because the value inserted into the trackartist column (3) sqlite> -- does not correspond to row in the artist table. sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', 3); SQL error: foreign key constraint failed sqlite> -- This succeeds because a NULL is inserted into trackartist. A sqlite> -- corresponding row in the artist table is not required in this case. sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL); sqlite> -- Trying to modify the trackartist field of the record after it has sqlite> -- been inserted does not work either, since the new value of trackartist (3) sqlite> -- Still does not correspond to any row in the artist table. sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; SQL error: foreign key constraint failed sqlite> -- Insert the required row into the artist table. It is then possible to sqlite> -- update the inserted row to set trackartist to 3 (since a corresponding sqlite> -- row in the artist table now exists). sqlite> INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; sqlite> -- Now that "Sammy Davis Jr." (artistid = 3) has been added to the database, sqlite> -- it is possible to INSERT new tracks using this artist without violating sqlite> -- the foreign key constraint: sqlite> INSERT INTO track VALUES(15, 'Boogie Woogie', 3);
As you would expect, it is not possible to manipulate the database to a state
that violates the foreign key constraint by deleting or updating rows in the
artist table either:
sqlite> -- Attempting to delete the artist record for "Frank Sinatra" fails, since
sqlite> -- the track table contains a row that refer to it.
sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra';
SQL error: foreign key constraint failed
sqlite> -- Delete all the records from the track table that refer to the artist
sqlite> -- "Frank Sinatra". Only then is it possible to delete the artist.
sqlite> DELETE FROM track WHERE trackname = 'My Way';
sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra';
sqlite> -- Try to update the artistid of a row in the artist table while there
sqlite> -- exists records in the track table that refer to it.
sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
SQL error: foreign key constraint failed
sqlite> -- Once all the records that refer to a row in the artist table have
sqlite> -- been deleted, it is possible to modify the artistid of the row.
sqlite> DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
SQLite uses the following terminology:
-
The parent table is the table that a foreign key constraint
refers to. The parent table in the example in this section is the
artist table. Some books and articles refer to this as the
referenced table, which is arguably more correct, but tends
to lead to confusion. -
The child table is the table that a foreign key constraint
is applied to and the table that contains the REFERENCES clause.
The example in this section uses the track table
as the child table. Other books and articles refer to this as the
referencing table. -
The parent key is the column or set of columns in the parent
table that the foreign key constraint refers to. This is normally, but
not always, the primary key of the parent table. The parent key must
be a named column or columns in the parent table, not the rowid. -
The child key is the column or set of columns in the child
table that are constrained by the foreign key constraint and which
hold the REFERENCES clause.
The foreign key constraint is satisfied if for each row in the child table
either one or more of the child key columns are NULL, or there exists a
row in the parent table for which each parent key column contains a value
equal to the value in its associated child key column.
In the above paragraph, the term «equal» means equal when values are
compared using the rules specified
here. The following clarifications apply:
-
When comparing text values, the collating sequence
associated with the parent key column is always used. -
When comparing values, if the parent key column has an affinity,
then that affinity is applied to the child key value before the
comparison is performed.
2.
Enabling Foreign Key Support
In order to use foreign key constraints in SQLite, the library must
be compiled with neither SQLITE_OMIT_FOREIGN_KEY nor
SQLITE_OMIT_TRIGGER defined. If SQLITE_OMIT_TRIGGER is defined
but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior
to version 3.6.19 (2009-10-14)
— foreign key definitions are parsed and may be
queried using PRAGMA foreign_key_list, but foreign key constraints
are not enforced. The PRAGMA foreign_keys command is a no-op in this
configuration. If OMIT_FOREIGN_KEY is defined, then foreign key
definitions cannot even be parsed (attempting to specify a foreign
key definition is a syntax error).
Assuming the library is compiled with foreign key constraints enabled,
it must still be enabled by the application at runtime, using the
PRAGMA foreign_keys command. For example:
sqlite> PRAGMA foreign_keys = ON;
Foreign key constraints are disabled by default
(for backwards compatibility),
so must be enabled separately for each database connection.
(Note, however, that future releases of SQLite might change
so that foreign key constraints enabled by default. Careful
developers will not
make any assumptions about whether or not foreign keys are enabled by
default but will instead enable or disable them as necessary.)
The application can also use a PRAGMA foreign_keys statement to
determine if foreign keys are currently enabled. The following
command-line session demonstrates this:
sqlite> PRAGMA foreign_keys; 0 sqlite> PRAGMA foreign_keys = ON; sqlite> PRAGMA foreign_keys; 1 sqlite> PRAGMA foreign_keys = OFF; sqlite> PRAGMA foreign_keys; 0
Tip: If the command «PRAGMA foreign_keys» returns no data instead of a
single row containing «0» or «1», then the version of SQLite you are
using does not support foreign keys (either because it is older than
3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or
SQLITE_OMIT_TRIGGER defined).
It is not possible to enable or disable foreign key constraints
in the middle of a multi-statement transaction (when SQLite
is not in autocommit mode). Attempting to do so does not return
an error; it simply has no effect.
3. Required and Suggested Database Indexes
Usually, the parent key of a foreign key constraint is the primary key of
the parent table. If they are not the primary key, then the parent key
columns must be collectively subject to a UNIQUE constraint or have
a UNIQUE index.
If the parent key columns have a UNIQUE index,
then that index must use the collation sequences that are specified
in the CREATE TABLE statement for the parent table.
For example,
CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f); CREATE UNIQUE INDEX i1 ON parent(c, d); CREATE INDEX i2 ON parent(e); CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase); CREATE TABLE child1(f, g REFERENCES parent(a)); -- Ok CREATE TABLE child2(h, i REFERENCES parent(b)); -- Ok CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); -- Ok CREATE TABLE child4(l, m REFERENCES parent(e)); -- Error! CREATE TABLE child5(n, o REFERENCES parent(f)); -- Error! CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c)); -- Error! CREATE TABLE child7(r REFERENCES parent(c)); -- Error!
The foreign key constraints created as part of tables child1,
child2 and child3 are all fine. The foreign key
declared as part of table child4 is an error because even though
the parent key column is indexed, the index is not UNIQUE.
The foreign key for table child5
is an error because even though the parent key column has a unique
index, the index uses a different collating sequence.
Tables child6 and child7 are incorrect because while
both have UNIQUE indices on their parent keys, the keys are not an
exact match to the columns of a single UNIQUE index.
If the database schema contains foreign key errors that require looking
at more than one table definition to identify, then those errors are not
detected when the tables are created. Instead, such errors prevent
the application from preparing SQL statements that modify the content
of the child or parent tables in ways that use the foreign keys.
Errors reported when content is changed are «DML errors» and errors
reported when the schema is changed are «DDL errors».
So, in other words, misconfigured foreign key constraints that require
looking at both the child and parent are DML errors.
The English language error message for foreign key DML errors is usually
«foreign key mismatch» but can also be «no such table» if the parent
table does not exist.
Foreign key DML errors are reported if:
- The parent table does not exist, or
- The parent key columns named in the foreign key constraint do
not exist, or - The parent key columns named in the foreign key constraint are not
the primary key of the parent table and are not subject to a unique
constraint using collating sequence specified in the CREATE TABLE, or - The child table references the primary key of the parent without
specifying the primary key columns and the number of primary key
columns in the parent do not match the number of child key columns.
The last bullet above is illustrated by the following:
CREATE TABLE parent2(a, b, PRIMARY KEY(a,b)); CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); -- Ok CREATE TABLE child9(x REFERENCES parent2); -- Error! CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); -- Error!
By contrast, if foreign key errors can be recognized simply by looking
at the definition of the child table and without having to consult the
parent table definition, then the
CREATE TABLE statement for the child table fails. Because the error
occurs during a schema change, this is a DDL error.
Foreign key DDL errors are reported regardless of
whether or not foreign key constraints are enabled when the
table is created.
Indices are not required for child key columns but they are almost
always beneficial. Returning to
the example in section 1, each time an application
deletes a row from the artist table (the parent table), it
performs the equivalent of the following SELECT statement to search
for referencing rows in the track table (the child table).
SELECT rowid FROM track WHERE trackartist = ?
where ? in the above is replaced with the value of the artistid
column of the record being deleted from the artist table (recall
that the trackartist column is the child key and the artistid
column is the parent key). Or, more generally:
SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
If this SELECT returns any rows at all, then SQLite concludes that
deleting the row from the parent table would violate the foreign key
constraint and returns an error.
Similar queries may be run if the content of the parent key
is modified or a new row is inserted into the parent table.
If these queries cannot use an index, they are forced to do a
linear scan of the entire child table. In a non-trivial database, this may
be prohibitively expensive.
So, in most real systems, an index should be created on the child key columns
of each foreign key constraint. The child key index does not have
to be (and usually will not be) a UNIQUE index.
Returning again to the example in section 1, the
complete database schema for efficient implementation of the foreign key
constraint might be:
CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist ); CREATE INDEX trackindex ON track(trackartist);
The block above uses a shorthand form to create the foreign key constraint.
Attaching a «REFERENCES <parent-table>» clause to a column
definition creates a foreign key constraint that maps the column to the
primary key of <parent-table>. Refer to the CREATE TABLE
documentation for further details.
4. Advanced Foreign Key Constraint Features
4.1. Composite Foreign Key Constraints
A composite foreign key constraint is one where the child and parent keys
are both composite keys. For example, consider
the following database schema:
CREATE TABLE album( albumartist TEXT, albumname TEXT, albumcover BINARY, PRIMARY KEY(albumartist, albumname) ); CREATE TABLE song( songid INTEGER, songartist TEXT, songalbum TEXT, songname TEXT, FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname) );
In this system, each entry in the song table is required to map to an entry
in the album table with the same combination of artist and album.
Parent and child keys must have the same cardinality.
In SQLite, if any of the child key columns (in this case songartist
and songalbum) are NULL, then there is no requirement for a corresponding
row in the parent table.
4.2. Deferred Foreign Key Constraints
Each foreign key constraint in SQLite is classified as either immediate
or deferred. Foreign key constraints are immediate by default.
All the foreign key examples presented
so far have been of immediate foreign key constraints.
If a statement modifies the contents of the database so that an immediate
foreign key constraint is in violation at the conclusion the statement,
an exception is thrown and
the effects of the statement are reverted. By contrast, if
a statement modifies the contents of the database such that a deferred
foreign key constraint is violated, the violation is not reported
immediately. Deferred foreign key constraints are not checked
until the transaction tries to COMMIT.
For as long as the user has
an open transaction, the database is allowed to exist in a state that
violates any number of deferred foreign key constraints. However,
COMMIT will fail as long as foreign key constraints remain in
violation.
If the current statement is not inside an explicit transaction (a
BEGIN/COMMIT/ROLLBACK block), then an implicit
transaction is committed
as soon as the statement has finished executing. In this case deferred
constraints behave the same as immediate constraints.
To mark a foreign key constraint as deferred, its declaration must
include the following clause:
DEFERRABLE INITIALLY DEFERRED -- A deferred foreign key constraint
The full syntax for specifying foreign key constraints is available as part
of the CREATE TABLE documentation. Replacing the phrase above
with any of the following
creates an immediate foreign key constraint.
NOT DEFERRABLE INITIALLY DEFERRED -- An immediate foreign key constraint NOT DEFERRABLE INITIALLY IMMEDIATE -- An immediate foreign key constraint NOT DEFERRABLE -- An immediate foreign key constraint DEFERRABLE INITIALLY IMMEDIATE -- An immediate foreign key constraint DEFERRABLE -- An immediate foreign key constraint
The defer_foreign_keys pragma can be used to temporarily change all foreign
key constraints to deferred regardless of how they are declared.
The following example illustrates the effect of using a deferred foreign
key constraint.
-- Database schema. Both tables are initially empty. CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED ); sqlite3> -- If the foreign key constraint were immediate, this INSERT would sqlite3> -- cause an error (since as there is no row in table artist with sqlite3> -- artistid=5). But as the constraint is deferred and there is an sqlite3> -- open transaction, no error occurs. sqlite3> BEGIN; sqlite3> INSERT INTO track VALUES(1, 'White Christmas', 5); sqlite3> -- The following COMMIT fails, as the database is in a state that sqlite3> -- does not satisfy the deferred foreign key constraint. The sqlite3> -- transaction remains open. sqlite3> COMMIT; SQL error: foreign key constraint failed sqlite3> -- After inserting a row into the artist table with artistid=5, the sqlite3> -- deferred foreign key constraint is satisfied. It is then possible sqlite3> -- to commit the transaction without error. sqlite3> INSERT INTO artist VALUES(5, 'Bing Crosby'); sqlite3> COMMIT;
A nested savepoint transaction may be RELEASEd while the
database is in a state that does not satisfy a deferred foreign key
constraint. A transaction savepoint (a non-nested savepoint that was
opened while there was not currently an open transaction), on the
other hand, is subject to the same restrictions as a COMMIT — attempting
to RELEASE it while the database is in such a state will fail.
If a COMMIT statement (or the RELEASE of a transaction SAVEPOINT) fails
because the database is currently in a state that violates a deferred
foreign key constraint and there are currently
nested savepoints, the nested savepoints remain open.
4.3.
ON DELETE and ON UPDATE Actions
Foreign key ON DELETE and ON UPDATE clauses are used to configure actions
that take place when deleting rows from the parent table (ON DELETE), or
modifying the parent key values of existing rows (ON UPDATE). A single
foreign key constraint may have different actions configured for ON DELETE
and ON UPDATE. Foreign key actions are similar to triggers in many ways.
The ON DELETE and ON UPDATE action associated with each foreign key in an
SQLite database is one of «NO ACTION», «RESTRICT», «SET NULL»,
«SET DEFAULT» or «CASCADE». If an action is not explicitly specified, it
defaults to «NO ACTION».
-
NO ACTION: Configuring «NO ACTION» means just that: when a
parent key is modified or deleted from the database, no special action is
taken. -
RESTRICT: The «RESTRICT» action means that the application
is prohibited from deleting (for ON DELETE RESTRICT) or modifying
(for ON UPDATE RESTRICT) a parent key when there exists one or more child
keys mapped to it. The difference between the effect of a RESTRICT
action and normal foreign key constraint enforcement is that the
RESTRICT action processing happens as soon as the field is updated —
not at the end of the current statement as it would with an immediate
constraint, or at the end of the current transaction as it would with
a deferred constraint.
Even if the foreign key constraint it is
attached to is deferred, configuring a RESTRICT action causes SQLite to
return an error immediately if a parent key with dependent child keys is
deleted or modified. -
SET NULL: If the configured action is «SET NULL», then when
a parent key is deleted (for ON DELETE SET NULL) or modified (for ON
UPDATE SET NULL), the child key columns of all rows in the child table
that mapped to the parent key are set to contain SQL NULL values. -
SET DEFAULT: The «SET DEFAULT» actions are similar to
«SET NULL»,
except that each of the child key columns is set to contain the column’s
default value instead of NULL. Refer to the CREATE TABLE
documentation for details on how default values are assigned to table
columns. -
CASCADE: A «CASCADE» action propagates the delete or update
operation on the parent key to each dependent child key. For an «ON
DELETE CASCADE» action, this means that each row in the child table that
was associated with the deleted parent row is also deleted. For an «ON
UPDATE CASCADE» action, it means that the values stored in each dependent
child key are modified to match the new parent key values.
For example, adding an «ON UPDATE CASCADE» clause to the foreign key as
shown below enhances the example schema from section 1 to allow the user
to update the artistid (the parent key of the foreign key constraint)
column without breaking referential integrity:
-- Database schema CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE ); sqlite> SELECT * FROM artist; artistid artistname -------- ----------------- 1 Dean Martin 2 Frank Sinatra sqlite> SELECT * FROM track; trackid trackname trackartist ------- ----------------- ----------- 11 That's Amore 1 12 Christmas Blues 1 13 My Way 2 sqlite> -- Update the artistid column of the artist record for "Dean Martin". sqlite> -- Normally, this would raise a constraint, as it would orphan the two sqlite> -- dependent records in the track table. However, the ON UPDATE CASCADE clause sqlite> -- attached to the foreign key definition causes the update to "cascade" sqlite> -- to the child table, preventing the foreign key constraint violation. sqlite> UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin'; sqlite> SELECT * FROM artist; artistid artistname -------- ----------------- 2 Frank Sinatra 100 Dean Martin sqlite> SELECT * FROM track; trackid trackname trackartist ------- ----------------- ----------- 11 That's Amore 100 12 Christmas Blues 100 13 My Way 2
Configuring an ON UPDATE or ON DELETE action does not mean that the foreign
key constraint does not need to be satisfied. For example, if an
«ON DELETE SET DEFAULT» action is configured,
but there is no row in the parent table
that corresponds to the default values of the child key columns, deleting
a parent key while dependent child keys exist still causes a foreign key
violation. For example:
-- Database schema CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT ); sqlite> SELECT * FROM artist; artistid artistname -------- ----------------- 3 Sammy Davis Jr. sqlite> SELECT * FROM track; trackid trackname trackartist ------- ----------------- ----------- 14 Mr. Bojangles 3 sqlite> -- Deleting the row from the parent table causes the child key sqlite> -- value of the dependent row to be set to integer value 0. However, this sqlite> -- value does not correspond to any row in the parent table. Therefore sqlite> -- the foreign key constraint is violated and an is exception thrown. sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.'; SQL error: foreign key constraint failed sqlite> -- This time, the value 0 does correspond to a parent table row. And sqlite> -- so the DELETE statement does not violate the foreign key constraint sqlite> -- and no exception is thrown. sqlite> INSERT INTO artist VALUES(0, 'Unknown Artist'); sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.'; sqlite> SELECT * FROM artist; artistid artistname -------- ----------------- 0 Unknown Artist sqlite> SELECT * FROM track; trackid trackname trackartist ------- ----------------- ----------- 14 Mr. Bojangles 0
Those familiar with SQLite triggers
will have noticed that the
«ON DELETE SET DEFAULT» action demonstrated in the example above is
similar in effect to the following AFTER DELETE trigger:
CREATE TRIGGER on_delete_set_default AFTER DELETE ON artist BEGIN UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid; END;
Whenever a row in the parent table of a foreign key constraint is deleted,
or when the values stored in the parent key column or columns are modified,
the logical sequence of events is:
- Execute applicable BEFORE trigger programs,
- Check local (non foreign key) constraints,
- Update or delete the row in the parent table,
- Perform any required foreign key actions,
- Execute applicable AFTER trigger programs.
There is one important difference between ON UPDATE foreign key actions and
SQL triggers. An ON UPDATE action is only taken if the values of the
parent key are modified so that the new parent key values are
not equal to the old. For example:
-- Database schema CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL); sqlite> SELECT * FROM parent; x ---- key sqlite> SELECT * FROM child; y ---- key sqlite> -- Since the following UPDATE statement does not actually modify sqlite> -- the parent key value, the ON UPDATE action is not performed and sqlite> -- the child key value is not set to NULL. sqlite> UPDATE parent SET x = 'key'; sqlite> SELECT IFNULL(y, 'null') FROM child; y ---- key sqlite> -- This time, since the UPDATE statement does modify the parent key sqlite> -- value, the ON UPDATE action is performed and the child key is set sqlite> -- to NULL. sqlite> UPDATE parent SET x = 'key2'; sqlite> SELECT IFNULL(y, 'null') FROM child; y ---- null
5. CREATE, ALTER and DROP TABLE commands
This section describes the way the CREATE TABLE, ALTER TABLE,
and DROP TABLE commands
interact with SQLite’s foreign keys.
A CREATE TABLE command operates the same whether or not
foreign key constraints are enabled. The parent key definitions of
foreign key constraints are not checked when a table is created. There is
nothing stopping the user from creating a foreign key definition that
refers to a parent table that does not exist, or to parent key columns that
do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE
constraint.
The ALTER TABLE command works differently in two respects when foreign
key constraints are enabled:
-
It is not possible to use the «ALTER TABLE … ADD COLUMN» syntax
to add a column that includes a REFERENCES clause, unless the default
value of the new column is NULL. Attempting to do so returns an
error. -
If an «ALTER TABLE … RENAME TO» command is used to rename a table
that is the parent table of one or more foreign key constraints, the
definitions of the foreign key constraints are modified to refer to
the parent table by its new name. The text of the child CREATE
TABLE statement or statements stored in the sqlite_schema table are
modified to reflect the new parent table name.
If foreign key constraints are enabled when it is prepared, the
DROP TABLE command performs an implicit DELETE to remove all
rows from the table before dropping it. The implicit DELETE does not cause
any SQL triggers to fire, but may invoke foreign key actions or constraint
violations. If an immediate foreign key constraint is violated, the DROP
TABLE statement fails and the table is not dropped. If a deferred foreign
key constraint is violated, then an error is reported when the user attempts
to commit the transaction if the foreign key constraint violations still
exist at that point. Any «foreign key mismatch» errors encountered as part
of an implicit DELETE are ignored.
The intent of these enhancements to the ALTER TABLE and DROP TABLE
commands is to ensure that they cannot be used to create a database that
contains foreign key violations, at least while foreign key constraints are
enabled. There is one exception to this rule though. If a parent key is
not subject to a PRIMARY KEY or UNIQUE constraint created as part of the
parent table definition, but is subject to a UNIQUE constraint by virtue
of an index created using the CREATE INDEX command, then the child
table may be populated without causing a «foreign key mismatch» error. If
the UNIQUE index is dropped from the database schema, then the parent table
itself is dropped, no error will be reported. However the database may be
left in a state where the child table of the foreign key constraint contains
rows that do not refer to any parent table row. This case can be avoided
if all parent keys in the database schema are constrained by PRIMARY KEY
or UNIQUE constraints added as part of the parent table definition, not
by external UNIQUE indexes.
The properties of the DROP TABLE and ALTER TABLE commands described
above only apply if foreign keys are enabled. If the user considers them
undesirable, then the workaround is to use PRAGMA foreign_keys to
disable foreign key constraints before executing the DROP or ALTER TABLE
command. Of course, while foreign key constraints are disabled, there is nothing
to stop the user from violating foreign key constraints and thus creating
an internally inconsistent database.
6. Limits and Unsupported Features
This section lists a few limitations and omitted features that are not
mentioned elsewhere.
-
No support for the MATCH clause. According to SQL92, a MATCH clause
may be attached to a composite foreign key definition to modify the way
NULL values that occur in child keys are handled. If «MATCH SIMPLE» is
specified, then a child key is not required to correspond to any row
of the parent table if one or more of the child key values are NULL.
If «MATCH FULL» is specified, then if any of the child key values is
NULL, no corresponding row in the parent table is required, but all
child key values must be NULL. Finally, if the foreign key constraint
is declared as «MATCH PARTIAL» and one of the child key values is NULL,
there must exist at least one row in the parent table for which the
non-NULL child key values match the parent key values.SQLite parses MATCH clauses (i.e. does not report a syntax error
if you specify one), but does not enforce them. All foreign key
constraints in SQLite are handled as if MATCH SIMPLE were specified. -
No support for switching constraints between deferred and immediate
mode. Many systems allow the user to toggle individual foreign key
constraints between deferred and immediate
mode at runtime (for example using the Oracle «SET CONSTRAINT» command).
SQLite does not support this. In SQLite, a foreign key constraint is
permanently marked as deferred or immediate when it is created. -
Recursion limit on foreign key actions. The
SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH
settings determine the maximum allowable depth of trigger
program recursion. For the purposes of these limits,
foreign key actions are considered trigger programs. The
PRAGMA recursive_triggers setting does not affect the operation
of foreign key actions. It is not possible to disable recursive foreign
key actions.