<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="constraint-foreign-key">
      </a>
      1.7.3.2 FOREIGN KEY Constraints
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045333127456">
  </a>
  <p>
   Foreign keys let you cross-reference related data across
          tables, and
   <a class="link" href="glossary.html#glos_foreign_key_constraint" title="FOREIGN KEY constraint">
    foreign key
          constraints
   </a>
   help keep this spread-out data consistent.
  </p>
  <p>
   MySQL supports
   <code class="literal">
    ON UPDATE
   </code>
   and
   <code class="literal">
    ON
          DELETE
   </code>
   foreign key references in
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   and
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statements. The
          available referential actions are
   <code class="literal">
    RESTRICT
   </code>
   ,
   <code class="literal">
    CASCADE
   </code>
   ,
   <code class="literal">
    SET NULL
   </code>
   , and
   <code class="literal">
    NO ACTION
   </code>
   (the default).
  </p>
  <p>
   <code class="literal">
    SET DEFAULT
   </code>
   is also supported by the MySQL
          Server but is currently rejected as invalid by
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   . Since MySQL does not
          support deferred constraint checking,
   <code class="literal">
    NO
          ACTION
   </code>
   is treated as
   <code class="literal">
    RESTRICT
   </code>
   .
          For the exact syntax supported by MySQL for foreign keys, 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>
  <p>
   <code class="literal">
    MATCH FULL
   </code>
   ,
   <code class="literal">
    MATCH
          PARTIAL
   </code>
   , and
   <code class="literal">
    MATCH SIMPLE
   </code>
   are
          allowed, but their use should be avoided, as they cause the
          MySQL Server to ignore any
   <code class="literal">
    ON DELETE
   </code>
   or
   <code class="literal">
    ON UPDATE
   </code>
   clause used in the same
          statement.
   <code class="literal">
    MATCH
   </code>
   options do not have any
          other effect in MySQL, which in effect enforces
   <code class="literal">
    MATCH
          SIMPLE
   </code>
   semantics full-time.
  </p>
  <p>
   MySQL requires that foreign key columns be indexed; if you
          create a table with a foreign key constraint but no index on a
          given column, an index is created.
  </p>
  <p>
   You can obtain information about foreign keys from the
          Information Schema
   <a class="link" href="information-schema-key-column-usage-table.html" title="28.3.16 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table">
    <code class="literal">
     KEY_COLUMN_USAGE
    </code>
   </a>
   table. An
          example of a query against this table is shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa12569565"><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">SELECT</span> TABLE_SCHEMA<span class="token punctuation">,</span> <span class="token keyword">TABLE_NAME</span><span class="token punctuation">,</span> <span class="token keyword">COLUMN_NAME</span><span class="token punctuation">,</span> <span class="token keyword">CONSTRAINT_NAME</span>
     <span class="token operator">&gt;</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>KEY_COLUMN_USAGE
     <span class="token operator">&gt;</span> <span class="token keyword">WHERE</span> REFERENCED_TABLE_SCHEMA <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><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 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 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 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> TABLE_SCHEMA <span class="token punctuation">|</span> TABLE_NAME    <span class="token punctuation">|</span> COLUMN_NAME <span class="token punctuation">|</span> CONSTRAINT_NAME <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 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 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 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> fk1          <span class="token punctuation">|</span> myuser        <span class="token punctuation">|</span> myuser_id   <span class="token punctuation">|</span> f               <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> fk1          <span class="token punctuation">|</span> product_order <span class="token punctuation">|</span> customer_id <span class="token punctuation">|</span> f2              <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> fk1          <span class="token punctuation">|</span> product_order <span class="token punctuation">|</span> product_id  <span class="token punctuation">|</span> f1              <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 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 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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">3 rows in set (0.01 sec)</span></code></pre>
  </div>
  <p>
   Information about foreign keys on
   <code class="literal">
    InnoDB
   </code>
   tables can also be found in the
   <a class="link" href="information-schema-innodb-foreign-table.html" title="28.4.12 The INFORMATION_SCHEMA INNODB_FOREIGN Table">
    <code class="literal">
     INNODB_FOREIGN
    </code>
   </a>
   and
   <a class="link" href="information-schema-innodb-foreign-cols-table.html" title="28.4.13 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table">
    <code class="literal">
     INNODB_FOREIGN_COLS
    </code>
   </a>
   tables, in
          the
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   database.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   and
   <code class="literal">
    NDB
   </code>
   tables
          support foreign keys.
  </p>
 </div>
 <br/>
</div>
