<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="example-foreign-keys">
      </a>
      5.6.6 Using Foreign Keys
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045324680736">
  </a>
  <a class="indexterm" name="idm46045324679664">
  </a>
  <p>
   MySQL supports foreign keys, which permit cross-referencing
        related data across tables, and foreign key constraints, which
        help keep the related data consistent.
  </p>
  <p>
   A foreign key relationship involves a parent table that holds
        the initial column values, and a child table with column values
        that reference the parent column values. A foreign key
        constraint is defined on the child table.
  </p>
  <p>
   This following example relates
   <code class="literal">
    parent
   </code>
   and
   <code class="literal">
    child
   </code>
   tables through a single-column foreign
        key and shows how a foreign key constraint enforces referential
        integrity.
  </p>
  <p>
   Create the parent and child tables using the following SQL
        statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90722243"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> parent <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>INNODB<span class="token punctuation">;</span>


<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> child <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span><span class="token punctuation">,</span>
    parent_id <span class="token datatype">INT</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> par_ind <span class="token punctuation">(</span>parent_id<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>parent_id<span class="token punctuation">)</span>
        <span class="token keyword">REFERENCES</span> parent<span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>INNODB<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Insert a row into the parent table, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa3335116"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> parent <span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Verify that the data was inserted. You can do this simply by
        selecting all rows from
   <code class="literal">
    parent
   </code>
   , as shown
        here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73073320"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> parent<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Insert a row into the child table using the following SQL
        statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa55900470"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> child <span class="token punctuation">(</span>id<span class="token punctuation">,</span>parent_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The insert operation is successful because
   <code class="literal">
    parent_id
   </code>
   1 is present in the parent table.
  </p>
  <p>
   Insertion of a row into the child table with a
   <code class="literal">
    parent_id
   </code>
   value that is not present in the
        parent table is rejected with an error, as you can see here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3616301"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> child <span class="token punctuation">(</span>id<span class="token punctuation">,</span>parent_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1452 (23000)<span class="token punctuation">:</span> Cannot add or update a child row<span class="token punctuation">:</span> a foreign key constraint fails 
(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) 
REFERENCES `parent` (`id`))</span></code></pre>
  </div>
  <p>
   The operation fails because the specified
   <code class="literal">
    parent_id
   </code>
   value does not exist in the parent
        table.
  </p>
  <p>
   Trying to delete the previously inserted row from the parent
        table also fails, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13175448"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> parent <span class="token keyword">WHERE</span> id <span class="token keyword">VALUES</span> <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1451 (23000)<span class="token punctuation">:</span> Cannot delete or update a parent row<span class="token punctuation">:</span> a foreign key constraint fails 
(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) 
REFERENCES `parent` (`id`))</span></code></pre>
  </div>
  <p>
   This operation fails because the record in the child table
        contains the referenced id (
   <code class="literal">
    parent_id
   </code>
   ) value.
  </p>
  <p>
   When an operation affects a key value in the parent table that
        has matching rows in the child table, the result depends on the
        referential action specified by
   <code class="literal">
    ON UPDATE
   </code>
   and
   <code class="literal">
    ON DELETE
   </code>
   subclauses of the
   <code class="literal">
    FOREIGN
        KEY
   </code>
   clause. Omitting
   <code class="literal">
    ON DELETE
   </code>
   and
   <code class="literal">
    ON UPDATE
   </code>
   clauses (as in the current child
        table definition) is the same as specifying the
   <code class="literal">
    RESTRICT
   </code>
   option, which rejects operations
        that affect a key value in the parent table that has matching
        rows in the parent table.
  </p>
  <p>
   To demonstrate
   <code class="literal">
    ON DELETE
   </code>
   and
   <code class="literal">
    ON
        UPDATE
   </code>
   referential actions, drop the child table and
        recreate it to include
   <code class="literal">
    ON UPDATE
   </code>
   and
   <code class="literal">
    ON DELETE
   </code>
   subclauses with the
   <code class="literal">
    CASCADE
   </code>
   option. The
   <code class="literal">
    CASCADE
   </code>
   option automatically deletes or
        updates matching rows in the child table when deleting or
        updating rows in the parent table.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46756594"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> child<span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> child <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span><span class="token punctuation">,</span>
    parent_id <span class="token datatype">INT</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> par_ind <span class="token punctuation">(</span>parent_id<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>parent_id<span class="token punctuation">)</span>
        <span class="token keyword">REFERENCES</span> parent<span class="token punctuation">(</span>id<span class="token punctuation">)</span>
        <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CASCADE</span>
        <span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">CASCADE</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>INNODB<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Insert some rows into the child table using the statement shown
        here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa62005697"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> child <span class="token punctuation">(</span>id<span class="token punctuation">,</span>parent_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Verify that the data was inserted, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37123045"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> child<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id   <span class="token punctuation">|</span> parent_id <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>         1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>         1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>         1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Update the ID in the parent table, changing it from 1 to 2,
        using the SQL statement shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa37202509"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">UPDATE</span> parent <span class="token keyword">SET</span> id <span class="token operator">=</span> <span class="token number">2</span> <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Verify that the update was successful by selecting all rows from
        the parent table, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa80993186"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> parent<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Verify that the
   <code class="literal">
    ON UPDATE CASCADE
   </code>
   referential
        action updated the child table, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7791824"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> child<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id   <span class="token punctuation">|</span> parent_id <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>         2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>         2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>         2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   To demonstrate the
   <code class="literal">
    ON DELETE CASCADE
   </code>
   referential action, delete records from the parent table where
   <code class="literal">
    parent_id = 2
   </code>
   ; this deletes all records in
        the parent table.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa25204273"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> parent <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Because all records in the child table are associated with
   <code class="literal">
    parent_id = 2
   </code>
   , the
   <code class="literal">
    ON DELETE
        CASCADE
   </code>
   referential action removes all records from
        the child table, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82073087"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> child<span class="token punctuation">;</span>
<span class="token output">Empty set (0.00 sec)</span></code></pre>
  </div>
  <p>
   For more information about foreign key constraints, see
   <a class="xref" href="create-table-foreign-keys.html" title="15.1.20.5 FOREIGN KEY Constraints">
    Section 15.1.20.5, “FOREIGN KEY Constraints”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
