diff options
Diffstat (limited to 'www/foreignkeys.html')
| -rw-r--r-- | www/foreignkeys.html | 1046 |
1 files changed, 1046 insertions, 0 deletions
diff --git a/www/foreignkeys.html b/www/foreignkeys.html new file mode 100644 index 0000000..31d7c78 --- /dev/null +++ b/www/foreignkeys.html @@ -0,0 +1,1046 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>SQLite Foreign Key Support</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> +<div class=fancy> +<div class=nosearch> +<div class="fancy_title"> +SQLite Foreign Key Support +</div> +<div class="fancy_toc"> +<a onclick="toggle_toc()"> +<span class="fancy_toc_mark" id="toc_mk">►</span> +Table Of Contents +</a> +<div id="toc_sub"><div class="fancy-toc1"><a href="#fk_basics">1. Introduction to Foreign Key Constraints</a></div> +<div class="fancy-toc1"><a href="#fk_enable">2. +Enabling Foreign Key Support +</a></div> +<div class="fancy-toc1"><a href="#fk_indexes">3. Required and Suggested Database Indexes</a></div> +<div class="fancy-toc1"><a href="#fk_advanced">4. Advanced Foreign Key Constraint Features</a></div> +<div class="fancy-toc2"><a href="#fk_composite">4.1. Composite Foreign Key Constraints</a></div> +<div class="fancy-toc2"><a href="#fk_deferred">4.2. Deferred Foreign Key Constraints</a></div> +<div class="fancy-toc2"><a href="#fk_actions">4.3. +ON DELETE and ON UPDATE Actions +</a></div> +<div class="fancy-toc1"><a href="#fk_schemacommands">5. CREATE, ALTER and DROP TABLE commands</a></div> +<div class="fancy-toc1"><a href="#fk_unsupported">6. Limits and Unsupported Features</a></div> +</div> +</div> +<script> +function toggle_toc(){ +var sub = document.getElementById("toc_sub") +var mk = document.getElementById("toc_mk") +if( sub.style.display!="block" ){ +sub.style.display = "block"; +mk.innerHTML = "▼"; +} else { +sub.style.display = "none"; +mk.innerHTML = "►"; +} +} +</script> +</div> + + + + + +<h2 style="margin-left:1.0em" notoc="1" id="overview"> Overview</h2> + +<p>This document describes the support for SQL foreign key constraints + introduced in SQLite <a href="releaselog/3_6_19.html">version 3.6.19</a> (2009-10-14). + +</p><p>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 <a href="lang_altertable.html">ALTER</a> and <a href="lang_droptable.html">DROP TABLE</a> commands are + enhanced to support foreign key constraints. Finally, section 6 + enumerates the missing features and limits of the current implementation. + +</p><p>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 <a href="lang_createtable.html">CREATE TABLE</a> statement. + +</p><h1 id="fk_basics"><span>1. </span>Introduction to Foreign Key Constraints</h1> + + <p> + SQL foreign key constraints are used to enforce "exists" relationships + between tables. For example, consider a database schema created using + the following SQL commands: + +</p><div class="codeblock"><pre>CREATE TABLE artist( + artistid INTEGER PRIMARY KEY, + artistname TEXT +); +CREATE TABLE track( + trackid INTEGER, + trackname TEXT, + trackartist INTEGER <b>-- Must map to an artist.artistid!</b> +); +</pre></div> + + <p> + The applications using this database are entitled to assume that for + each row in the <i>track</i> table there exists a corresponding row in the + <i>artist</i> 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 + <i>track</i> table that do not correspond to any row in the <i>artist</i> + table. Or rows might be deleted from the <i>artist</i> table, leaving + orphaned rows in the <i>track</i> table that do not correspond to any of + the remaining rows in <i>artist</i>. This might cause the application + or applications to malfunction later on, or at least make coding the + application more difficult. + + </p><p> + One solution is to add an SQL foreign key constraint to the database + schema to enforce the relationship between the <i>artist</i> and + <i>track</i> table. To do so, a foreign key definition may be added + by modifying the declaration of the <i>track</i> table to the following: + +</p><div class="codeblock"><pre>CREATE TABLE track( + trackid INTEGER, + trackname TEXT, + trackartist INTEGER, + <b>FOREIGN KEY(trackartist) REFERENCES artist(artistid)</b> +); +</pre></div> + + <p> + This way, the constraint is enforced by SQLite. Attempting to insert + a row into the <i>track</i> table that does not correspond to any + row in the <i>artist</i> table will fail, as will attempting to + delete a row from the <i>artist</i> table when there exist dependent + rows in the <i>track</i> table There is one exception: if the foreign + key column in the <i>track</i> table is NULL, then no corresponding + entry in the <i>artist</i> table is required. Expressed in SQL, this + means that for every row in the <i>track</i> table, the following + expression evaluates to true: +</p><div class="codeblock"><pre>trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) +</pre></div> + + <p>Tip: If the application requires a stricter relationship between + <i>artist</i> and <i>track</i>, where NULL values are not permitted + in the <i>trackartist</i> column, simply add the appropriate + "NOT NULL" constraint to the schema. + + </p><p>There are several other ways to add an equivalent foreign key declaration + to a <a href="lang_createtable.html">CREATE TABLE</a> statement. Refer to the + <a href="lang_createtable.html">CREATE TABLE documentation</a> for details. + + </p><p>The following SQLite command-line session illustrates the effect of the + foreign key constraint added to the <i>track</i> table: + +</p><div class="codeblock"><pre>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> <i>-- This fails because the value inserted into the trackartist column (3)</i> +sqlite> <i>-- does not correspond to row in the artist table.</i> +sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', 3); +<b>SQL error: foreign key constraint failed</b> + +sqlite> <i>-- This succeeds because a NULL is inserted into trackartist. A</i> +sqlite> <i>-- corresponding row in the artist table is not required in this case.</i> +sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL); + +sqlite> <i>-- Trying to modify the trackartist field of the record after it has </i> +sqlite> <i>-- been inserted does not work either, since the new value of trackartist (3)</i> +sqlite> <i>-- Still does not correspond to any row in the artist table.</i> +sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; +<b>SQL error: foreign key constraint failed</b> + +sqlite> <i>-- Insert the required row into the artist table. It is then possible to</i> +sqlite> <i>-- update the inserted row to set trackartist to 3 (since a corresponding</i> +sqlite> <i>-- row in the artist table now exists).</i> +sqlite> INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); +sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; + +sqlite> <i>-- Now that "Sammy Davis Jr." (artistid = 3) has been added to the database,</i> +sqlite> <i>-- it is possible to INSERT new tracks using this artist without violating</i> +sqlite> <i>-- the foreign key constraint:</i> +sqlite> INSERT INTO track VALUES(15, 'Boogie Woogie', 3); +</pre></div> + + <p> + 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 + <i>artist</i> table either: +</p><div class="codeblock"><pre>sqlite> <i>-- Attempting to delete the artist record for "Frank Sinatra" fails, since</i> +sqlite> <i>-- the track table contains a row that refer to it.</i> +sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra'; +<b>SQL error: foreign key constraint failed</b> + +sqlite> <i>-- Delete all the records from the track table that refer to the artist</i> +sqlite> <i>-- "Frank Sinatra". Only then is it possible to delete the artist.</i> +sqlite> DELETE FROM track WHERE trackname = 'My Way'; +sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra'; + +sqlite> <i>-- Try to update the artistid of a row in the artist table while there</i> +sqlite> <i>-- exists records in the track table that refer to it. </i> +sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; +<b>SQL error: foreign key constraint failed</b> + +sqlite> <i>-- Once all the records that refer to a row in the artist table have</i> +sqlite> <i>-- been deleted, it is possible to modify the artistid of the row.</i> +sqlite> DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues'); +sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; +</pre></div> + + <p> + SQLite uses the following terminology: +<a name="parentchild"></a> + + </p><ul> + <li><p>The <b>parent table</b> is the table that a foreign key constraint + refers to. The parent table in the example in this section is the + <i>artist</i> table. Some books and articles refer to this as the + <i>referenced table</i>, which is arguably more correct, but tends + to lead to confusion. + + </p></li><li><p>The <b>child table</b> 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 <i>track</i> table + as the child table. Other books and articles refer to this as the + <i>referencing table</i>. + + </p></li><li><p>The <b>parent key</b> 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 <a href="lang_createtable.html#rowid">rowid</a>. + + </p></li><li><p>The <b>child key</b> 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. + </p></li></ul> + + <p> + 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. + + </p><p> + In the above paragraph, the term "equal" means equal when values are + compared using the rules <a href="datatype3.html#comparisons">specified + here</a>. The following clarifications apply: + + </p><ul> + <li><p>When comparing text values, the <a href="datatype3.html#collation">collating sequence</a> + associated with the parent key column is always used. + </p></li><li><p>When comparing values, if the parent key column has an <a href="datatype3.html#affinity">affinity</a>, + then that affinity is applied to the child key value before the + comparison is performed. + </p></li></ul> + +<a name="fk_enable"></a> +<h1 id="fk_enable" tags="foreign key constraints are enabled"><span>2. </span> + Enabling Foreign Key Support +</h1> + <p> + In order to use foreign key constraints in SQLite, the library must + be compiled with neither <a href="compile.html#omit_foreign_key">SQLITE_OMIT_FOREIGN_KEY</a> nor + <a href="compile.html#omit_trigger">SQLITE_OMIT_TRIGGER</a> defined. If SQLITE_OMIT_TRIGGER is defined + but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior + to <a href="releaselog/3_6_19.html">version 3.6.19</a> (2009-10-14) + - foreign key definitions are parsed and may be + queried using <a href="pragma.html#pragma_foreign_key_list">PRAGMA foreign_key_list</a>, but foreign key constraints + are not enforced. The <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys</a> 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). + + </p><p> + Assuming the library is compiled with foreign key constraints enabled, + it must still be enabled by the application at runtime, using the + <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys</a> command. For example: + +</p><div class="codeblock"><pre>sqlite> PRAGMA foreign_keys = ON; +</pre></div> + + <p> + Foreign key constraints are disabled by default + (for backwards compatibility), + so must be enabled separately for each <a href="c3ref/sqlite3.html">database connection</a>. + (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 <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys</a> statement to + determine if foreign keys are currently enabled. The following + command-line session demonstrates this: +</p><div class="codeblock"><pre>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 +</pre></div> + + <p>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 <a href="compile.html#omit_foreign_key">SQLITE_OMIT_FOREIGN_KEY</a> or + <a href="compile.html#omit_trigger">SQLITE_OMIT_TRIGGER</a> defined). + + </p><p> + It is not possible to enable or disable foreign key constraints + in the middle of a <a href="lang_transaction.html">multi-statement transaction</a> (when SQLite + is not in <a href="c3ref/get_autocommit.html">autocommit mode</a>). Attempting to do so does not return + an error; it simply has no effect. + +</p><h1 id="fk_indexes"><span>3. </span>Required and Suggested Database Indexes</h1> + + <p> + 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, + +</p><div class="codeblock"><pre>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)); <i>-- Ok</i> +CREATE TABLE child2(h, i REFERENCES parent(b)); <i>-- Ok</i> +CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); <i>-- Ok</i> +CREATE TABLE child4(l, m REFERENCES parent(e)); <i>-- Error!</i> +CREATE TABLE child5(n, o REFERENCES parent(f)); <i>-- Error!</i> +CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c)); <i>-- Error!</i> +CREATE TABLE child7(r REFERENCES parent(c)); <i>-- Error!</i> +</pre></div> + + <p> + The foreign key constraints created as part of tables <i>child1</i>, + <i>child2</i> and <i>child3</i> are all fine. The foreign key + declared as part of table <i>child4</i> is an error because even though + the parent key column is indexed, the index is not UNIQUE. + The foreign key for table <i>child5</i> + is an error because even though the parent key column has a unique + index, the index uses a different collating sequence. + Tables <i>child6</i> and <i>child7</i> 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. + + </p><p> + 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: + + </p><ul> + <li> The parent table does not exist, or + </li><li> The parent key columns named in the foreign key constraint do + not exist, or + </li><li> 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 + </li><li> 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. + </li></ul> + + <p> + The last bullet above is illustrated by the following: + +</p><div class="codeblock"><pre>CREATE TABLE parent2(a, b, PRIMARY KEY(a,b)); + +CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); <i>-- Ok</i> +CREATE TABLE child9(x REFERENCES parent2); <i>-- Error!</i> +CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); <i>-- Error!</i> +</pre></div> + + <p> + 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 + <a href="lang_createtable.html">CREATE TABLE</a> 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. + + </p><p> + Indices are not required for child key columns but they are almost + always beneficial. Returning to + the example in <a href="#fk_basics">section 1</a>, each time an application + deletes a row from the <i>artist</i> table (the parent table), it + performs the equivalent of the following SELECT statement to search + for referencing rows in the <i>track</i> table (the child table). + +</p><div class="codeblock"><pre>SELECT rowid FROM track WHERE trackartist = ? +</pre></div> + + <p> + where ? in the above is replaced with the value of the <i>artistid</i> + column of the record being deleted from the <i>artist</i> table (recall + that the <i>trackartist</i> column is the child key and the <i>artistid</i> + column is the parent key). Or, more generally: + +</p><div class="codeblock"><pre>SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value +</pre></div> + + <p> + 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. + + </p><p> + 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: + +</p><div class="codeblock"><pre>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); +</pre></div> + + <p> + The block above uses a shorthand form to create the foreign key constraint. + Attaching a "REFERENCES <i><parent-table></i>" clause to a column + definition creates a foreign key constraint that maps the column to the + primary key of <i><parent-table></i>. Refer to the <a href="lang_createtable.html">CREATE TABLE</a> + documentation for further details. + + +</p><h1 id="fk_advanced"><span>4. </span>Advanced Foreign Key Constraint Features</h1> + +<h2 id="fk_composite"><span>4.1. </span>Composite Foreign Key Constraints</h2> + + <p> + A composite foreign key constraint is one where the child and parent keys + are both composite keys. For example, consider + the following database schema: + +</p><div class="codeblock"><pre>CREATE TABLE album( + albumartist TEXT, + albumname TEXT, + albumcover BINARY, + PRIMARY KEY(albumartist, albumname) +); + +CREATE TABLE song( + songid INTEGER, + songartist TEXT, + songalbum TEXT, + songname TEXT, + <b>FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)</b> +); +</pre></div> + + <p> + 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. + + </p><p> + 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. + +</p><h2 id="fk_deferred"><span>4.2. </span>Deferred Foreign Key Constraints</h2> + + <p> + 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. + + </p><p> + 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 <a href="lang_transaction.html">COMMIT</a>. + 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, + <a href="lang_transaction.html">COMMIT</a> will fail as long as foreign key constraints remain in + violation. + + </p><p> + If the current statement is not inside an explicit transaction (a + <a href="lang_transaction.html">BEGIN</a>/<a href="lang_transaction.html">COMMIT</a>/<a href="lang_transaction.html">ROLLBACK</a> 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. + + </p><p> + To mark a foreign key constraint as deferred, its declaration must + include the following clause: + +</p><div class="codeblock"><pre>DEFERRABLE INITIALLY DEFERRED <i>-- A deferred foreign key constraint</i> +</pre></div> + + <p> + The full syntax for specifying foreign key constraints is available as part + of the <a href="lang_createtable.html">CREATE TABLE</a> documentation. Replacing the phrase above + with any of the following + creates an immediate foreign key constraint. + +</p><div class="codeblock"><pre>NOT DEFERRABLE INITIALLY DEFERRED <i>-- An immediate foreign key constraint</i> +NOT DEFERRABLE INITIALLY IMMEDIATE <i>-- An immediate foreign key constraint</i> +NOT DEFERRABLE <i>-- An immediate foreign key constraint</i> +DEFERRABLE INITIALLY IMMEDIATE <i>-- An immediate foreign key constraint</i> +DEFERRABLE <i>-- An immediate foreign key constraint</i> +</pre></div> + + <p>The <a href="pragma.html#pragma_defer_foreign_keys">defer_foreign_keys pragma</a> can be used to temporarily change all foreign + key constraints to deferred regardless of how they are declared. + + </p><p> + The following example illustrates the effect of using a deferred foreign + key constraint. + +</p><div class="codeblock"><pre><i>-- Database schema. Both tables are initially empty. </i> +CREATE TABLE artist( + artistid INTEGER PRIMARY KEY, + artistname TEXT +); +CREATE TABLE track( + trackid INTEGER, + trackname TEXT, + trackartist INTEGER REFERENCES artist(artistid) <b>DEFERRABLE INITIALLY DEFERRED</b> +); + +sqlite3> <i>-- If the foreign key constraint were immediate, this INSERT would</i> +sqlite3> <i>-- cause an error (since as there is no row in table artist with</i> +sqlite3> <i>-- artistid=5). But as the constraint is deferred and there is an</i> +sqlite3> <i>-- open transaction, no error occurs.</i> +sqlite3> BEGIN; +sqlite3> INSERT INTO track VALUES(1, 'White Christmas', 5); + +sqlite3> <i>-- The following COMMIT fails, as the database is in a state that</i> +sqlite3> <i>-- does not satisfy the deferred foreign key constraint. The</i> +sqlite3> <i>-- transaction remains open.</i> +sqlite3> COMMIT; +<b>SQL error: foreign key constraint failed</b> + +sqlite3> <i>-- After inserting a row into the artist table with artistid=5, the</i> +sqlite3> <i>-- deferred foreign key constraint is satisfied. It is then possible</i> +sqlite3> <i>-- to commit the transaction without error.</i> +sqlite3> INSERT INTO artist VALUES(5, 'Bing Crosby'); +sqlite3> COMMIT; +</pre></div> + <p> + A <a href="lang_savepoint.html">nested savepoint</a> 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. + + </p><p> + 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 + <a href="lang_savepoint.html">nested savepoints</a>, the nested savepoints remain open. + + +</p><a name="fk_actions"></a> +<h2 id="fk_actions" tags="foreign key actions"><span>4.3. </span> + ON DELETE and ON UPDATE Actions +</h2> + + <p> + 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. + + </p><p> + 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". + + </p><ul> + <li><p> <b>NO ACTION</b>: Configuring "NO ACTION" means just that: when a + parent key is modified or deleted from the database, no special action is + taken. + + </p></li><li><p> <b>RESTRICT</b>: 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. + + </p></li><li><p> <b>SET NULL</b>: 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. + + </p></li><li><p> <b>SET DEFAULT</b>: 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 <a href="lang_createtable.html">CREATE TABLE</a> + documentation for details on how default values are assigned to table + columns. + + </p></li><li><p> <b>CASCADE</b>: 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. + </p></li></ul> + + <p> + 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: +</p><div class="codeblock"><pre><i>-- Database schema</i> +CREATE TABLE artist( + artistid INTEGER PRIMARY KEY, + artistname TEXT +); +CREATE TABLE track( + trackid INTEGER, + trackname TEXT, + trackartist INTEGER REFERENCES artist(artistid) <b>ON UPDATE CASCADE</b> +); + +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> <i>-- Update the artistid column of the artist record for "Dean Martin".</i> +sqlite> <i>-- Normally, this would raise a constraint, as it would orphan the two</i> +sqlite> <i>-- dependent records in the track table. However, the ON UPDATE CASCADE clause</i> +sqlite> <i>-- attached to the foreign key definition causes the update to "cascade"</i> +sqlite> <i>-- to the child table, preventing the foreign key constraint violation.</i> +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 +</pre></div> + + <p> + 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: + +</p><div class="codeblock"><pre><i>-- Database schema</i> +CREATE TABLE artist( + artistid INTEGER PRIMARY KEY, + artistname TEXT +); +CREATE TABLE track( + trackid INTEGER, + trackname TEXT, + trackartist INTEGER <b>DEFAULT 0</b> REFERENCES artist(artistid) <b>ON DELETE SET DEFAULT</b> +); + +sqlite> SELECT * FROM artist; +artistid artistname +-------- ----------------- +3 Sammy Davis Jr. + +sqlite> SELECT * FROM track; +trackid trackname trackartist +------- ----------------- ----------- +14 Mr. Bojangles 3 + +sqlite> <i>-- Deleting the row from the parent table causes the child key</i> +sqlite> <i>-- value of the dependent row to be set to integer value 0. However, this</i> +sqlite> <i>-- value does not correspond to any row in the parent table. Therefore</i> +sqlite> <i>-- the foreign key constraint is violated and an is exception thrown.</i> +sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.'; +<b>SQL error: foreign key constraint failed</b> + +sqlite> <i>-- This time, the value 0 does correspond to a parent table row. And</i> +sqlite> <i>-- so the DELETE statement does not violate the foreign key constraint</i> +sqlite> <i>-- and no exception is thrown.</i> +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 +</pre></div> + + <p> + Those familiar with <a href="lang_createtrigger.html">SQLite triggers</a> + 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: +</p><div class="codeblock"><pre>CREATE TRIGGER on_delete_set_default AFTER DELETE ON artist BEGIN + UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid; +END; +</pre></div> + + <p> + 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: + + </p><ol> + <li> Execute applicable BEFORE trigger programs, + </li><li> Check local (non foreign key) constraints, + </li><li> Update or delete the row in the parent table, + </li><li> Perform any required foreign key actions, + </li><li> Execute applicable AFTER trigger programs. + </li></ol> + + <p> + 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: + +</p><div class="codeblock"><pre><i>-- Database schema</i> +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> <i>-- Since the following UPDATE statement does not actually modify</i> +sqlite> <i>-- the parent key value, the ON UPDATE action is not performed and</i> +sqlite> <i>-- the child key value is not set to NULL.</i> +sqlite> UPDATE parent SET x = 'key'; +sqlite> SELECT IFNULL(y, 'null') FROM child; +y +---- +key + +sqlite> <i>-- This time, since the UPDATE statement does modify the parent key</i> +sqlite> <i>-- value, the ON UPDATE action is performed and the child key is set</i> +sqlite> <i>-- to NULL.</i> +sqlite> UPDATE parent SET x = 'key2'; +sqlite> SELECT IFNULL(y, 'null') FROM child; +y +---- +null +</pre></div> + +<h1 id="fk_schemacommands"><span>5. </span>CREATE, ALTER and DROP TABLE commands</h1> + + <p> + This section describes the way the <a href="lang_createtable.html">CREATE TABLE</a>, <a href="lang_altertable.html">ALTER TABLE</a>, + and <a href="lang_droptable.html">DROP TABLE</a> commands + interact with SQLite's foreign keys. + + </p><p> + A <a href="lang_createtable.html">CREATE TABLE</a> command operates the same whether or not + <a href="foreignkeys.html#fk_enable">foreign key constraints are enabled</a>. 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. + + </p><p> + The <a href="lang_altertable.html">ALTER TABLE</a> command works differently in two respects when foreign + key constraints are enabled: + + </p><ul> + <li><p> + 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. + + </p></li><li><p> + 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 <a href="schematab.html">sqlite_schema table</a> are + modified to reflect the new parent table name. + </p></li></ul> + + <p> + If foreign key constraints are enabled when it is prepared, the + <a href="lang_droptable.html">DROP TABLE</a> command performs an implicit <a href="lang_delete.html">DELETE</a> 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. + + </p><p> + The intent of these enhancements to the <a href="lang_altertable.html">ALTER TABLE</a> and <a href="lang_droptable.html">DROP TABLE</a> + 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 <a href="lang_createindex.html">CREATE INDEX</a> 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. + + </p><p> + The properties of the <a href="lang_droptable.html">DROP TABLE</a> and <a href="lang_altertable.html">ALTER TABLE</a> commands described + above only apply if foreign keys are enabled. If the user considers them + undesirable, then the workaround is to use <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys</a> 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. + + + +</p><h1 id="fk_unsupported"><span>6. </span>Limits and Unsupported Features</h1> + + <p> + This section lists a few limitations and omitted features that are not + mentioned elsewhere. + + </p><ol> + <li><p> + <b>No support for the MATCH clause.</b> 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. + + </p><p> + 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. + </p></li><li> <p> + <b>No support for switching constraints between deferred and immediate + mode.</b> Many systems allow the user to toggle individual foreign key + constraints between <a href="#fk_deferred">deferred</a> 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. + + </p></li><li><p> + <b>Recursion limit on foreign key actions.</b> The + <a href="limits.html#max_trigger_depth">SQLITE_MAX_TRIGGER_DEPTH</a> and <a href="c3ref/c_limit_attached.html#sqlitelimittriggerdepth">SQLITE_LIMIT_TRIGGER_DEPTH</a> + settings determine the maximum allowable depth of trigger + program recursion. For the purposes of these limits, + <a href="foreignkeys.html#fk_actions">foreign key actions</a> are considered trigger programs. The + <a href="pragma.html#pragma_recursive_triggers">PRAGMA recursive_triggers</a> setting does not affect the operation + of foreign key actions. It is not possible to disable recursive foreign + key actions. + </p></li></ol> +<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/foreignkeys.in?m=65cb8c8c4c85c4682">2022-01-20 21:38:08</a> UTC </small></i></p> + |