summaryrefslogtreecommitdiffstats
path: root/www/foreignkeys.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 14:07:11 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 14:07:11 +0000
commit63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree01c7571c7c762ceee70638549a99834fdd7c411b /www/foreignkeys.html
parentInitial commit. (diff)
downloadsqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz
sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip
Adding upstream version 3.45.1.upstream/3.45.1
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'www/foreignkeys.html')
-rw-r--r--www/foreignkeys.html1051
1 files changed, 1051 insertions, 0 deletions
diff --git a/www/foreignkeys.html b/www/foreignkeys.html
new file mode 100644
index 0000000..c0f3b2f
--- /dev/null
+++ b/www/foreignkeys.html
@@ -0,0 +1,1051 @@
+<!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">&#x25ba;</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 = "&#x25bc;";
+} else {
+sub.style.display = "none";
+mk.innerHTML = "&#x25ba;";
+}
+}
+</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&gt; 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&gt; <i>-- This fails because the value inserted into the trackartist column (3)</i>
+sqlite&gt; <i>-- does not correspond to row in the artist table.</i>
+sqlite&gt; INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
+<b>SQL error: foreign key constraint failed</b>
+
+sqlite&gt; <i>-- This succeeds because a NULL is inserted into trackartist. A</i>
+sqlite&gt; <i>-- corresponding row in the artist table is not required in this case.</i>
+sqlite&gt; INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL);
+
+sqlite&gt; <i>-- Trying to modify the trackartist field of the record after it has </i>
+sqlite&gt; <i>-- been inserted does not work either, since the new value of trackartist (3)</i>
+sqlite&gt; <i>-- Still does not correspond to any row in the artist table.</i>
+sqlite&gt; UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
+<b>SQL error: foreign key constraint failed</b>
+
+sqlite&gt; <i>-- Insert the required row into the artist table. It is then possible to</i>
+sqlite&gt; <i>-- update the inserted row to set trackartist to 3 (since a corresponding</i>
+sqlite&gt; <i>-- row in the artist table now exists).</i>
+sqlite&gt; INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
+sqlite&gt; UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
+
+sqlite&gt; <i>-- Now that "Sammy Davis Jr." (artistid = 3) has been added to the database,</i>
+sqlite&gt; <i>-- it is possible to INSERT new tracks using this artist without violating</i>
+sqlite&gt; <i>-- the foreign key constraint:</i>
+sqlite&gt; 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&gt; <i>-- Attempting to delete the artist record for "Frank Sinatra" fails, since</i>
+sqlite&gt; <i>-- the track table contains a row that refer to it.</i>
+sqlite&gt; DELETE FROM artist WHERE artistname = 'Frank Sinatra';
+<b>SQL error: foreign key constraint failed</b>
+
+sqlite&gt; <i>-- Delete all the records from the track table that refer to the artist</i>
+sqlite&gt; <i>-- "Frank Sinatra". Only then is it possible to delete the artist.</i>
+sqlite&gt; DELETE FROM track WHERE trackname = 'My Way';
+sqlite&gt; DELETE FROM artist WHERE artistname = 'Frank Sinatra';
+
+sqlite&gt; <i>-- Try to update the artistid of a row in the artist table while there</i>
+sqlite&gt; <i>-- exists records in the track table that refer to it. </i>
+sqlite&gt; UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
+<b>SQL error: foreign key constraint failed</b>
+
+sqlite&gt; <i>-- Once all the records that refer to a row in the artist table have</i>
+sqlite&gt; <i>-- been deleted, it is possible to modify the artistid of the row.</i>
+sqlite&gt; DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
+sqlite&gt; 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&gt; 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 &lt;child-table&gt; WHERE &lt;child-key&gt; = :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>&lt;parent-table&gt;</i>" clause to a column
+ definition creates a foreign key constraint that maps the column to the
+ primary key of <i>&lt;parent-table&gt;</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&gt; <i>-- If the foreign key constraint were immediate, this INSERT would</i>
+sqlite3&gt; <i>-- cause an error (since as there is no row in table artist with</i>
+sqlite3&gt; <i>-- artistid=5). But as the constraint is deferred and there is an</i>
+sqlite3&gt; <i>-- open transaction, no error occurs.</i>
+sqlite3&gt; BEGIN;
+sqlite3&gt; INSERT INTO track VALUES(1, 'White Christmas', 5);
+
+sqlite3&gt; <i>-- The following COMMIT fails, as the database is in a state that</i>
+sqlite3&gt; <i>-- does not satisfy the deferred foreign key constraint. The</i>
+sqlite3&gt; <i>-- transaction remains open.</i>
+sqlite3&gt; COMMIT;
+<b>SQL error: foreign key constraint failed</b>
+
+sqlite3&gt; <i>-- After inserting a row into the artist table with artistid=5, the</i>
+sqlite3&gt; <i>-- deferred foreign key constraint is satisfied. It is then possible</i>
+sqlite3&gt; <i>-- to commit the transaction without error.</i>
+sqlite3&gt; INSERT INTO artist VALUES(5, 'Bing Crosby');
+sqlite3&gt; 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&gt; SELECT * FROM artist;
+artistid artistname
+-------- -----------------
+1 Dean Martin
+2 Frank Sinatra
+
+sqlite&gt; SELECT * FROM track;
+trackid trackname trackartist
+------- ----------------- -----------
+11 That's Amore 1
+12 Christmas Blues 1
+13 My Way 2
+
+sqlite&gt; <i>-- Update the artistid column of the artist record for "Dean Martin".</i>
+sqlite&gt; <i>-- Normally, this would raise a constraint, as it would orphan the two</i>
+sqlite&gt; <i>-- dependent records in the track table. However, the ON UPDATE CASCADE clause</i>
+sqlite&gt; <i>-- attached to the foreign key definition causes the update to "cascade"</i>
+sqlite&gt; <i>-- to the child table, preventing the foreign key constraint violation.</i>
+sqlite&gt; UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin';
+
+sqlite&gt; SELECT * FROM artist;
+artistid artistname
+-------- -----------------
+2 Frank Sinatra
+100 Dean Martin
+
+sqlite&gt; 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&gt; SELECT * FROM artist;
+artistid artistname
+-------- -----------------
+3 Sammy Davis Jr.
+
+sqlite&gt; SELECT * FROM track;
+trackid trackname trackartist
+------- ----------------- -----------
+14 Mr. Bojangles 3
+
+sqlite&gt; <i>-- Deleting the row from the parent table causes the child key</i>
+sqlite&gt; <i>-- value of the dependent row to be set to integer value 0. However, this</i>
+sqlite&gt; <i>-- value does not correspond to any row in the parent table. Therefore</i>
+sqlite&gt; <i>-- the foreign key constraint is violated and an is exception thrown.</i>
+sqlite&gt; DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
+<b>SQL error: foreign key constraint failed</b>
+
+sqlite&gt; <i>-- This time, the value 0 does correspond to a parent table row. And</i>
+sqlite&gt; <i>-- so the DELETE statement does not violate the foreign key constraint</i>
+sqlite&gt; <i>-- and no exception is thrown.</i>
+sqlite&gt; INSERT INTO artist VALUES(0, 'Unknown Artist');
+sqlite&gt; DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
+
+sqlite&gt; SELECT * FROM artist;
+artistid artistname
+-------- -----------------
+0 Unknown Artist
+
+sqlite&gt; 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&gt; SELECT * FROM parent;
+x
+----
+key
+
+sqlite&gt; SELECT * FROM child;
+y
+----
+key
+
+sqlite&gt; <i>-- Since the following UPDATE statement does not actually modify</i>
+sqlite&gt; <i>-- the parent key value, the ON UPDATE action is not performed and</i>
+sqlite&gt; <i>-- the child key value is not set to NULL.</i>
+sqlite&gt; UPDATE parent SET x = 'key';
+sqlite&gt; SELECT IFNULL(y, 'null') FROM child;
+y
+----
+key
+
+sqlite&gt; <i>-- This time, since the UPDATE statement does modify the parent key</i>
+sqlite&gt; <i>-- value, the ON UPDATE action is performed and the child key is set</i>
+sqlite&gt; <i>-- to NULL.</i>
+sqlite&gt; UPDATE parent SET x = 'key2';
+sqlite&gt; 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><li><p>
+ <b>Foreign keys may not cross schema boundaries.</b> That is, in
+ <code>REFERENCES (X.Y)</code> table <code>X</code> will only be resolved
+ within the schema which contains the <code>REFERENCES</code> clause.
+ </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=a8733ccf86">2024-01-23 13:08:17</a> UTC </small></i></p>
+