<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="create-table-foreign-keys">
      </a>
      15.1.20.5 FOREIGN KEY Constraints
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045185188544">
  </a>
  <a class="indexterm" name="idm46045185187472">
  </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>
   The essential syntax for a defining a foreign key constraint in
        a
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement includes
        the following:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5127113"><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 punctuation">[</span><span class="token keyword">CONSTRAINT</span> <span class="token punctuation">[</span><em class="replaceable">symbol</em><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><em class="replaceable">index_name</em><span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">col_name</em><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
    <span class="token keyword">REFERENCES</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">(</span><em class="replaceable">col_name</em><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
    <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <em class="replaceable">reference_option</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <em class="replaceable">reference_option</em><span class="token punctuation">]</span>

<em class="replaceable">reference_option</em>:
    <span class="token keyword">RESTRICT</span> <span class="token operator">|</span> <span class="token keyword">CASCADE</span> <span class="token operator">|</span> <span class="token keyword">SET</span> <span class="token boolean">NULL</span> <span class="token operator">|</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span> <span class="token operator">|</span> <span class="token keyword">SET</span> <span class="token keyword">DEFAULT</span></code></pre>
  </div>
  <p>
   Foreign key constraint usage is described under the following
        topics in this section:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="create-table-foreign-keys.html#foreign-key-identifiers" title="Identifiers">
       Identifiers
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table-foreign-keys.html#foreign-key-restrictions" title="Conditions and Restrictions">
       Conditions and Restrictions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table-foreign-keys.html#foreign-key-referential-actions" title="Referential Actions">
       Referential Actions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table-foreign-keys.html#foreign-key-examples" title="Foreign Key Constraint Examples">
       Foreign Key Constraint Examples
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table-foreign-keys.html#foreign-key-adding" title="Adding Foreign Key Constraints">
       Adding Foreign Key Constraints
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table-foreign-keys.html#foreign-key-dropping" title="Dropping Foreign Key Constraints">
       Dropping Foreign Key Constraints
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table-foreign-keys.html#foreign-key-checks" title="Foreign Key Checks">
       Foreign Key Checks
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table-foreign-keys.html#foreign-key-locking" title="Locking">
       Locking
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table-foreign-keys.html#foreign-key-metadata" title="Foreign Key Definitions and Metadata">
       Foreign Key Definitions and Metadata
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table-foreign-keys.html#foreign-key-errors" title="Foreign Key Errors">
       Foreign Key Errors
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="foreign-key-identifiers">
       </a>
       Identifiers
      </h5>
     </div>
    </div>
   </div>
   <p>
    Foreign key constraint naming is governed by the following
          rules:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The
       <code class="literal">
        CONSTRAINT
       </code>
       <em class="replaceable">
        <code>
         symbol
        </code>
       </em>
       value is used, if
              defined.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the
       <code class="literal">
        CONSTRAINT
       </code>
       <em class="replaceable">
        <code>
         symbol
        </code>
       </em>
       clause is not defined,
              or a symbol is not included following the
       <code class="literal">
        CONSTRAINT
       </code>
       keyword, a constraint name
              name is generated automatically.
      </p>
      <p>
       If the
       <code class="literal">
        CONSTRAINT
       </code>
       <em class="replaceable">
        <code>
         symbol
        </code>
       </em>
       clause is not defined,
              or a symbol is not included following the
       <code class="literal">
        CONSTRAINT
       </code>
       keyword, both
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       and
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       storage engines ignore
       <code class="literal">
        FOREIGN_KEY
        <em class="replaceable">
         <code>
          index_name
         </code>
        </em>
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        CONSTRAINT
        <em class="replaceable">
         <code>
          symbol
         </code>
        </em>
       </code>
       value, if
              defined, must be unique in the database. A duplicate
       <em class="replaceable">
        <code>
         symbol
        </code>
       </em>
       results in an error
              similar to:
       <span class="errortext">
        ERROR 1005 (HY000): Can't create
              table 'test.fk1' (errno: 121)
       </span>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       NDB Cluster stores foreign key names using the same
              lettercase with which they are created.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Table and column identifiers in a
    <code class="literal">
     FOREIGN KEY ...
          REFERENCES
    </code>
    clause can be quoted within backticks
          (
    <code class="literal">
     `
    </code>
    ). Alternatively, double quotation marks
          (
    <code class="literal">
     "
    </code>
    ) can be used if the
    <a class="link" href="sql-mode.html#sqlmode_ansi_quotes">
     <code class="literal">
      ANSI_QUOTES
     </code>
    </a>
    SQL mode is
          enabled. The
    <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
     <code class="literal">
      lower_case_table_names
     </code>
    </a>
    system
          variable setting is also taken into account.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="foreign-key-restrictions">
       </a>
       Conditions and Restrictions
      </h5>
     </div>
    </div>
   </div>
   <p>
    Foreign key constraints are subject to the following
          conditions and restrictions:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Parent and child tables must use the same storage engine,
              and they cannot be defined as temporary tables.
      </p>
     </li>
     <li class="listitem">
      <p>
       Creating a foreign key constraint requires the
       <a class="link" href="privileges-provided.html#priv_references">
        <code class="literal">
         REFERENCES
        </code>
       </a>
       privilege on the
              parent table.
      </p>
     </li>
     <li class="listitem">
      <p>
       Corresponding columns in the foreign key and the
              referenced key must have similar data types.
       <span class="emphasis">
        <em>
         The
              size and sign of fixed precision types such as
         <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
          <code class="literal">
           INTEGER
          </code>
         </a>
         and
         <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
          <code class="literal">
           DECIMAL
          </code>
         </a>
         must be the
              same
        </em>
       </span>
       . The length of string types need not be
              the same. For nonbinary (character) string columns, the
              character set and collation must be the same.
      </p>
     </li>
     <li class="listitem">
      <p>
       MySQL supports foreign key references between one column
              and another within a table. (A column cannot have a
              foreign key reference to itself.) In these cases, a
       <span class="quote">
        “
        <span class="quote">
         child table record
        </span>
        ”
       </span>
       refers to a dependent
              record within the same table.
      </p>
     </li>
     <li class="listitem">
      <p>
       MySQL requires indexes on foreign keys and referenced keys
              so that foreign key checks can be fast and not require a
              table scan. In the referencing table, there must be an
              index where the foreign key columns are listed as the
       <span class="emphasis">
        <em>
         first
        </em>
       </span>
       columns in the same order. Such
              an index is created on the referencing table automatically
              if it does not exist. This index might be silently dropped
              later if you create another index that can be used to
              enforce the foreign key constraint.
       <em class="replaceable">
        <code>
         index_name
        </code>
       </em>
       , if given, is used
              as described previously.
      </p>
     </li>
     <li class="listitem">
      <p>
       Previously,
       <code class="literal">
        InnoDB
       </code>
       allowed a foreign
              key to reference any index column or group of columns,
              even a non-unique index or partial index, an extension of
              standard SQL. This is still allowed for backwards
              compatibility, but is now deprecated; in addition, it must
              be enabled by setting
       <a class="link" href="server-system-variables.html#sysvar_restrict_fk_on_non_standard_key">
        <code class="literal">
         restrict_fk_on_non_standard_key
        </code>
       </a>
       .
              If this is done, there must still be an index in the
              referenced table where the referenced columns are the
       <span class="emphasis">
        <em>
         first
        </em>
       </span>
       columns in the same order.
              Hidden columns that
       <code class="literal">
        InnoDB
       </code>
       adds to an
              index are also considered in such cases (see
       <a class="xref" href="innodb-index-types.html" title="17.6.2.1 Clustered and Secondary Indexes">
        Section 17.6.2.1, “Clustered and Secondary Indexes”
       </a>
       ). You should expect
              support for use of nonstandard keys to be removed in a
              future version of MySQL, and migrate away from their use.
      </p>
      <p>
       <code class="literal">
        NDB
       </code>
       always requires an explicit unique
              key (or primary key) on any column referenced as a foreign
              key.
      </p>
     </li>
     <li class="listitem">
      <p>
       Index prefixes on foreign key columns are not supported.
              Consequently,
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         BLOB
        </code>
       </a>
       and
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         TEXT
        </code>
       </a>
       columns cannot be
              included in a foreign key because indexes on those columns
              must always include a prefix length.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       does not currently
              support foreign keys for tables with user-defined
              partitioning. This includes both parent and child tables.
      </p>
      <p>
       This restriction does not apply for
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       tables that are
              partitioned by
       <code class="literal">
        KEY
       </code>
       or
       <code class="literal">
        LINEAR
              KEY
       </code>
       (the only user partitioning types supported
              by the
       <code class="literal">
        NDB
       </code>
       storage engine); these may
              have foreign key references or be the targets of such
              references.
      </p>
     </li>
     <li class="listitem">
      <p>
       A table in a foreign key relationship cannot be altered to
              use another storage engine. To change the storage engine,
              you must drop any foreign key constraints first.
      </p>
     </li>
     <li class="listitem">
      <p>
       A foreign key constraint cannot reference a virtual
              generated column.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For information about how the MySQL implementation of foreign
          key constraints differs from the SQL standard, see
    <a class="xref" href="ansi-diff-foreign-keys.html" title="1.7.2.3 FOREIGN KEY Constraint Differences">
     Section 1.7.2.3, “FOREIGN KEY Constraint Differences”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="foreign-key-referential-actions">
       </a>
       Referential Actions
      </h5>
     </div>
    </div>
   </div>
   <p>
    When an
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
     </code>
    </a>
    or
    <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
     <code class="literal">
      DELETE
     </code>
    </a>
    operation affects a key
          value in the parent table that has matching rows in the child
          table, the result depends on the
    <span class="emphasis">
     <em>
      referential
          action
     </em>
    </span>
    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. Referential actions
          include:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        CASCADE
       </code>
       : Delete or update the row from
              the parent table and automatically delete or update the
              matching rows in the child table. Both
       <code class="literal">
        ON DELETE
              CASCADE
       </code>
       and
       <code class="literal">
        ON UPDATE CASCADE
       </code>
       are supported. Between two tables, do not define several
       <code class="literal">
        ON UPDATE CASCADE
       </code>
       clauses that act on
              the same column in the parent table or in the child table.
      </p>
      <p>
       If a
       <code class="literal">
        FOREIGN KEY
       </code>
       clause is defined on
              both tables in a foreign key relationship, making both
              tables a parent and child, an
       <code class="literal">
        ON UPDATE
              CASCADE
       </code>
       or
       <code class="literal">
        ON DELETE CASCADE
       </code>
       subclause defined for one
       <code class="literal">
        FOREIGN KEY
       </code>
       clause must be defined for the other in order for
              cascading operations to succeed. If an
       <code class="literal">
        ON UPDATE
              CASCADE
       </code>
       or
       <code class="literal">
        ON DELETE CASCADE
       </code>
       subclause is only defined for one
       <code class="literal">
        FOREIGN
              KEY
       </code>
       clause, cascading operations fail with an
              error.
      </p>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        Cascaded foreign key actions do not activate triggers.
       </p>
      </div>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        SET NULL
       </code>
       : Delete or update the row from
              the parent table and set the foreign key column or columns
              in the child table to
       <code class="literal">
        NULL
       </code>
       . Both
       <code class="literal">
        ON DELETE SET NULL
       </code>
       and
       <code class="literal">
        ON
              UPDATE SET NULL
       </code>
       clauses are supported.
      </p>
      <p>
       If you specify a
       <code class="literal">
        SET NULL
       </code>
       action,
       <span class="emphasis">
        <em>
         make sure that you have not declared the columns
              in the child table as
         <code class="literal">
          NOT
              NULL
         </code>
        </em>
       </span>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        RESTRICT
       </code>
       : Rejects the delete or update
              operation for the parent table. Specifying
       <code class="literal">
        RESTRICT
       </code>
       (or
       <code class="literal">
        NO
              ACTION
       </code>
       ) is the same as omitting the
       <code class="literal">
        ON
              DELETE
       </code>
       or
       <code class="literal">
        ON UPDATE
       </code>
       clause.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        NO ACTION
       </code>
       : A keyword from standard SQL.
              For
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       , this is
              equivalent to
       <code class="literal">
        RESTRICT
       </code>
       ; the delete or
              update operation for the parent table is immediately
              rejected if there is a related foreign key value in the
              referenced table.
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       supports deferred checks, and
       <code class="literal">
        NO ACTION
       </code>
       specifies a deferred check; when this is used, constraint
              checks are not performed until commit time. Note that for
       <code class="literal">
        NDB
       </code>
       tables, this causes all foreign key
              checks made for both parent and child tables to be
              deferred.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        SET DEFAULT
       </code>
       : This action is recognized
              by the MySQL parser, but both
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       and
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       reject table definitions
              containing
       <code class="literal">
        ON DELETE SET DEFAULT
       </code>
       or
       <code class="literal">
        ON UPDATE SET DEFAULT
       </code>
       clauses.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For storage engines that support foreign keys, MySQL rejects
          any
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT
     </code>
    </a>
    or
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
     </code>
    </a>
    operation that attempts
          to create a foreign key value in a child table if there is no
          matching candidate key value in the parent table.
   </p>
   <p>
    For an
    <code class="literal">
     ON DELETE
    </code>
    or
    <code class="literal">
     ON
          UPDATE
    </code>
    that is not specified, the default action is
          always
    <code class="literal">
     NO ACTION
    </code>
    .
   </p>
   <p>
    As the default, an
    <code class="literal">
     ON DELETE NO ACTION
    </code>
    or
    <code class="literal">
     ON UPDATE NO ACTION
    </code>
    clause that is
          specified explicitly does not appear in
    <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
     <code class="literal">
      SHOW CREATE TABLE
     </code>
    </a>
    output or in
          tables dumped with
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    .
    <code class="literal">
     RESTRICT
    </code>
    , which is an equivalent
          non-default keyword, appears in
    <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
     <code class="literal">
      SHOW
          CREATE TABLE
     </code>
    </a>
    output and in tables dumped with
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    .
   </p>
   <p>
    For
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    tables,
    <code class="literal">
     ON
          UPDATE CASCADE
    </code>
    is not supported where the reference
          is to the parent table's primary key.
   </p>
   <p>
    For
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    tables,
    <code class="literal">
     ON
          DELETE CASCADE
    </code>
    is not supported where the child
          table contains one or more columns of any of the
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      TEXT
     </code>
    </a>
    or
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      BLOB
     </code>
    </a>
    types. (Bug #89511, Bug
          #27484882)
   </p>
   <p>
    <code class="literal">
     InnoDB
    </code>
    performs cascading operations using
          a depth-first search algorithm on the records of the index
          that corresponds to the foreign key constraint.
   </p>
   <p>
    A foreign key constraint on a stored generated column cannot
          use
    <code class="literal">
     CASCADE
    </code>
    ,
    <code class="literal">
     SET NULL
    </code>
    ,
          or
    <code class="literal">
     SET DEFAULT
    </code>
    as
    <code class="literal">
     ON
          UPDATE
    </code>
    referential actions, nor can it use
    <code class="literal">
     SET NULL
    </code>
    or
    <code class="literal">
     SET DEFAULT
    </code>
    as
    <code class="literal">
     ON DELETE
    </code>
    referential actions.
   </p>
   <p>
    A foreign key constraint on the base column of a stored
          generated column cannot use
    <code class="literal">
     CASCADE
    </code>
    ,
    <code class="literal">
     SET NULL
    </code>
    , or
    <code class="literal">
     SET DEFAULT
    </code>
    as
    <code class="literal">
     ON UPDATE
    </code>
    or
    <code class="literal">
     ON
          DELETE
    </code>
    referential actions.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="foreign-key-examples">
       </a>
       Foreign Key Constraint Examples
      </h5>
     </div>
    </div>
   </div>
   <p>
    This simple example relates
    <code class="literal">
     parent
    </code>
    and
    <code class="literal">
     child
    </code>
    tables through a single-column
          foreign key:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97247049"><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 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>
    This is a more complex example in which a
    <code class="literal">
     product_order
    </code>
    table has foreign keys for
          two other tables. One foreign key references a two-column
          index in the
    <code class="literal">
     product
    </code>
    table. The other
          references a single-column index in the
    <code class="literal">
     customer
    </code>
    table:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68877522"><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> product <span class="token punctuation">(</span>
    category <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><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>
    price <span class="token datatype">DECIMAL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>category<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> customer <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> product_order <span class="token punctuation">(</span>
    <span class="token keyword">no</span> <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    product_category <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    product_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    customer_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><span class="token keyword">no</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>product_category<span class="token punctuation">,</span> product_id<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>customer_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>product_category<span class="token punctuation">,</span> product_id<span class="token punctuation">)</span>
      <span class="token keyword">REFERENCES</span> product<span class="token punctuation">(</span>category<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">RESTRICT</span><span class="token punctuation">,</span>

    <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>customer_id<span class="token punctuation">)</span>
      <span class="token keyword">REFERENCES</span> customer<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>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="foreign-key-adding">
       </a>
       Adding Foreign Key Constraints
      </h5>
     </div>
    </div>
   </div>
   <p>
    You can add a foreign key constraint to an existing table
          using the following
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    syntax:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31848981"><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">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em>
    <span class="token keyword">ADD</span> <span class="token punctuation">[</span><span class="token keyword">CONSTRAINT</span> <span class="token punctuation">[</span><em class="replaceable">symbol</em><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><em class="replaceable">index_name</em><span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">col_name</em><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
    <span class="token keyword">REFERENCES</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">(</span><em class="replaceable">col_name</em><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
    <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <em class="replaceable">reference_option</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <em class="replaceable">reference_option</em><span class="token punctuation">]</span></code></pre>
   </div>
   <p>
    The foreign key can be self referential (referring to the same
          table). When you add a foreign key constraint to a table using
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    ,
    <span class="emphasis">
     <em>
      remember
          to first create an index on the column(s) referenced by the
          foreign key.
     </em>
    </span>
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="foreign-key-dropping">
       </a>
       Dropping Foreign Key Constraints
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045185015248">
   </a>
   <a class="indexterm" name="idm46045185014208">
   </a>
   <a class="indexterm" name="idm46045185012720">
   </a>
   <p>
    You can drop a foreign key constraint using the following
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    syntax:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa4135751"><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">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">DROP</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <em class="replaceable">fk_symbol</em><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If the
    <code class="literal">
     FOREIGN KEY
    </code>
    clause defined a
    <code class="literal">
     CONSTRAINT
    </code>
    name when you created the
          constraint, you can refer to that name to drop the foreign key
          constraint. Otherwise, a constraint name was generated
          internally, and you must use that value. To determine the
          foreign key constraint name, use
    <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
     <code class="literal">
      SHOW
          CREATE TABLE
     </code>
    </a>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa18063647"><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">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> child\G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> child
Create Table<span class="token punctuation">:</span> CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
  REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> child <span class="token keyword">DROP</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">`</span>child_ibfk_1<span class="token punctuation">`</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Adding and dropping a foreign key in the same
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    statement is
          supported for
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE ...
          ALGORITHM=INPLACE
     </code>
    </a>
    . It is not supported for
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE ...
          ALGORITHM=COPY
     </code>
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="foreign-key-checks">
       </a>
       Foreign Key Checks
      </h5>
     </div>
    </div>
   </div>
   <p>
    In MySQL, InnoDB and NDB tables support checking of foreign
          key constraints. Foreign key checking is controlled by the
    <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
     <code class="literal">
      foreign_key_checks
     </code>
    </a>
    variable,
          which is enabled by default. Typically, you leave this
          variable enabled during normal operation to enforce
          referential integrity. The
    <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
     <code class="literal">
      foreign_key_checks
     </code>
    </a>
    variable
          has the same effect on
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    tables
          as it does for
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    tables.
   </p>
   <p>
    The
    <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
     <code class="literal">
      foreign_key_checks
     </code>
    </a>
    variable is dynamic and supports both global and session
          scopes. For information about using system variables, see
    <a class="xref" href="using-system-variables.html" title="7.1.9 Using System Variables">
     Section 7.1.9, “Using System Variables”
    </a>
    .
   </p>
   <p>
    Disabling foreign key checking is useful when:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Dropping a table that is referenced by a foreign key
              constraint. A referenced table can only be dropped after
       <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
        <code class="literal">
         foreign_key_checks
        </code>
       </a>
       is
              disabled. When you drop a table, constraints defined on
              the table are also dropped.
      </p>
     </li>
     <li class="listitem">
      <p>
       Reloading tables in different order than required by their
              foreign key relationships. For example,
       <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
        <span class="command">
         <strong>
          mysqldump
         </strong>
        </span>
       </a>
       produces correct definitions
              of tables in the dump file, including foreign key
              constraints for child tables. To make it easier to reload
              dump files for tables with foreign key relationships,
       <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
        <span class="command">
         <strong>
          mysqldump
         </strong>
        </span>
       </a>
       automatically includes a
              statement in the dump output that disables
       <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
        <code class="literal">
         foreign_key_checks
        </code>
       </a>
       . This
              enables you to import the tables in any order in case the
              dump file contains tables that are not correctly ordered
              for foreign keys. Disabling
       <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
        <code class="literal">
         foreign_key_checks
        </code>
       </a>
       also
              speeds up the import operation by avoiding foreign key
              checks.
      </p>
     </li>
     <li class="listitem">
      <p>
       Executing
       <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
        <code class="literal">
         LOAD DATA
        </code>
       </a>
       operations, to avoid foreign key checking.
      </p>
     </li>
     <li class="listitem">
      <p>
       Performing an
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       operation on a table that has a foreign key relationship.
      </p>
     </li>
    </ul>
   </div>
   <p>
    When
    <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
     <code class="literal">
      foreign_key_checks
     </code>
    </a>
    is
          disabled, foreign key constraints are ignored, with the
          following exceptions:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Recreating a table that was previously dropped returns an
              error if the table definition does not conform to the
              foreign key constraints that reference the table. The
              table must have the correct column names and types. It
              must also have indexes on the referenced keys. If these
              requirements are not satisfied, MySQL returns Error 1005
              that refers to errno: 150 in the error message, which
              means that a foreign key constraint was not correctly
              formed.
      </p>
     </li>
     <li class="listitem">
      <p>
       Altering a table returns an error (errno: 150) if a
              foreign key definition is incorrectly formed for the
              altered table.
      </p>
     </li>
     <li class="listitem">
      <p>
       Dropping an index required by a foreign key constraint.
              The foreign key constraint must be removed before dropping
              the index.
      </p>
     </li>
     <li class="listitem">
      <p>
       Creating a foreign key constraint where a column
              references a nonmatching column type.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Disabling
    <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
     <code class="literal">
      foreign_key_checks
     </code>
    </a>
    has these additional implications:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       It is permitted to drop a database that contains tables
              with foreign keys that are referenced by tables outside
              the database.
      </p>
     </li>
     <li class="listitem">
      <p>
       It is permitted to drop a table with foreign keys
              referenced by other tables.
      </p>
     </li>
     <li class="listitem">
      <p>
       Enabling
       <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
        <code class="literal">
         foreign_key_checks
        </code>
       </a>
       does
              not trigger a scan of table data, which means that rows
              added to a table while
       <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
        <code class="literal">
         foreign_key_checks
        </code>
       </a>
       is
              disabled are not checked for consistency when
       <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
        <code class="literal">
         foreign_key_checks
        </code>
       </a>
       is
              re-enabled.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="foreign-key-locking">
       </a>
       Locking
      </h5>
     </div>
    </div>
   </div>
   <p>
    MySQL extends metadata locks, as necessary, to tables that are
          related by a foreign key constraint. Extending metadata locks
          prevents conflicting DML and DDL operations from executing
          concurrently on related tables. This feature also enables
          updates to foreign key metadata when a parent table is
          modified. In earlier MySQL releases, foreign key metadata,
          which is owned by the child table, could not be updated
          safely.
   </p>
   <p>
    If a table is locked explicitly with
    <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
     <code class="literal">
      LOCK
          TABLES
     </code>
    </a>
    , any tables related by a foreign key
          constraint are opened and locked implicitly. For foreign key
          checks, a shared read-only lock
          (
    <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
     <code class="literal">
      LOCK TABLES
          READ
     </code>
    </a>
    ) is taken on related tables. For cascading
          updates, a shared-nothing write lock
          (
    <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
     <code class="literal">
      LOCK TABLES
          WRITE
     </code>
    </a>
    ) is taken on related tables that are involved
          in the operation.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="foreign-key-metadata">
       </a>
       Foreign Key Definitions and Metadata
      </h5>
     </div>
    </div>
   </div>
   <p>
    To view a foreign key definition, use
    <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
     <code class="literal">
      SHOW CREATE TABLE
     </code>
    </a>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49912100"><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">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> child\G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> child
Create Table<span class="token punctuation">:</span> CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
  REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</span></code></pre>
   </div>
   <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="sa1566367"><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 keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>KEY_COLUMN_USAGE
       <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>
<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>
<span class="token output"><span class="token punctuation">|</span> test         <span class="token punctuation">|</span> child      <span class="token punctuation">|</span> parent_id   <span class="token punctuation">|</span> child_ibfk_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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
    You can obtain information specific to
    <code class="literal">
     InnoDB
    </code>
    foreign keys from 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.
          Example queries are show here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15190380"><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> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_FOREIGN \G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
      ID<span class="token punctuation">:</span> test/child_ibfk_1
FOR_NAME<span class="token punctuation">:</span> test/child
REF_NAME<span class="token punctuation">:</span> test/parent
  N_COLS<span class="token punctuation">:</span> 1
    TYPE<span class="token punctuation">:</span> 1
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_FOREIGN_COLS \G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
          ID<span class="token punctuation">:</span> test/child_ibfk_1
FOR_COL_NAME<span class="token punctuation">:</span> parent_id
REF_COL_NAME<span class="token punctuation">:</span> id
         POS<span class="token punctuation">:</span> 0</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="foreign-key-errors">
       </a>
       Foreign Key Errors
      </h5>
     </div>
    </div>
   </div>
   <p>
    In the event of a foreign key error involving
    <code class="literal">
     InnoDB
    </code>
    tables (usually Error 150 in the
          MySQL Server), information about the latest foreign key error
          can be obtained by checking
    <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
     <code class="literal">
      SHOW ENGINE
          INNODB STATUS
     </code>
    </a>
    output.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55493185"><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">SHOW</span> <span class="token keyword">ENGINE</span> INNODB <span class="token keyword">STATUS</span>\G
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token comment" spellcheck="true">------------------------</span>
LATEST <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token keyword">ERROR</span>
<span class="token comment" spellcheck="true">------------------------</span>
<span class="token number">2018</span><span class="token operator">-</span><span class="token number">04</span><span class="token operator">-</span><span class="token number">12</span> <span class="token number">14</span>:<span class="token number">57</span>:<span class="token number">24</span> <span class="token number">0x7f97a9c91700</span> <span class="token keyword">Transaction</span>:
<span class="token keyword">TRANSACTION</span> <span class="token number">7717</span><span class="token punctuation">,</span> <span class="token keyword">ACTIVE</span> <span class="token number">0</span> sec inserting
mysql <span class="token keyword">tables</span> <span class="token keyword">in</span> <span class="token keyword">use</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token keyword">locked</span> <span class="token number">1</span>
<span class="token number">4</span> <span class="token keyword">lock</span> struct<span class="token punctuation">(</span>s<span class="token punctuation">)</span><span class="token punctuation">,</span> heap size <span class="token number">1136</span><span class="token punctuation">,</span> <span class="token number">3</span> <span class="token keyword">row</span> <span class="token keyword">lock</span><span class="token punctuation">(</span>s<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">undo</span> log entries <span class="token number">3</span>
MySQL thread id <span class="token number">8</span><span class="token punctuation">,</span> OS thread handle <span class="token number">140289365317376</span><span class="token punctuation">,</span> <span class="token keyword">query</span> id <span class="token number">14</span> localhost root <span class="token keyword">update</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> child <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token boolean">NULL</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 punctuation">(</span><span class="token boolean">NULL</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 punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token number">6</span><span class="token punctuation">)</span>
<span class="token keyword">Foreign</span> <span class="token keyword">key</span> <span class="token keyword">constraint</span> fails <span class="token keyword">for</span> <span class="token keyword">table</span> <span class="token punctuation">`</span>test<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>child<span class="token punctuation">`</span>:
<span class="token punctuation">,</span>
  <span class="token keyword">CONSTRAINT</span> <span class="token punctuation">`</span>child_ibfk_1<span class="token punctuation">`</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>parent_id<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> <span class="token punctuation">`</span>parent<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">ON</span> <span class="token keyword">DELETE</span>
  <span class="token keyword">CASCADE</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CASCADE</span>
Trying <span class="token keyword">to</span> <span class="token keyword">add</span> <span class="token keyword">in</span> child <span class="token keyword">table</span><span class="token punctuation">,</span> <span class="token keyword">in</span> <span class="token keyword">index</span> par_ind tuple:
<span class="token keyword">DATA</span> TUPLE: <span class="token number">2</span> <span class="token keyword">fields</span><span class="token punctuation">;</span>
 <span class="token number">0</span>: len <span class="token number">4</span><span class="token punctuation">;</span> hex <span class="token number">80000003</span><span class="token punctuation">;</span> <span class="token keyword">asc</span>     <span class="token punctuation">;</span><span class="token punctuation">;</span>
 <span class="token number">1</span>: len <span class="token number">4</span><span class="token punctuation">;</span> hex <span class="token number">80000003</span><span class="token punctuation">;</span> <span class="token keyword">asc</span>     <span class="token punctuation">;</span><span class="token punctuation">;</span>

But <span class="token keyword">in</span> parent <span class="token keyword">table</span> <span class="token punctuation">`</span>test<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>parent<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token keyword">in</span> <span class="token keyword">index</span> <span class="token keyword">PRIMARY</span><span class="token punctuation">,</span>
the closest <span class="token operator">match</span> we can find <span class="token operator">is</span> record:
PHYSICAL RECORD: n_fields <span class="token number">3</span><span class="token punctuation">;</span> <span class="token keyword">compact</span> <span class="token keyword">format</span><span class="token punctuation">;</span> info bits <span class="token number">0</span>
 <span class="token number">0</span>: len <span class="token number">4</span><span class="token punctuation">;</span> hex <span class="token number">80000004</span><span class="token punctuation">;</span> <span class="token keyword">asc</span>     <span class="token punctuation">;</span><span class="token punctuation">;</span>
 <span class="token number">1</span>: len <span class="token number">6</span><span class="token punctuation">;</span> hex <span class="token number">000000001e19</span><span class="token punctuation">;</span> <span class="token keyword">asc</span>       <span class="token punctuation">;</span><span class="token punctuation">;</span>
 <span class="token number">2</span>: len <span class="token number">7</span><span class="token punctuation">;</span> hex <span class="token number">81000001110137</span><span class="token punctuation">;</span> <span class="token keyword">asc</span>       <span class="token number">7</span><span class="token punctuation">;</span><span class="token punctuation">;</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Warning
    </div>
    <p>
     If a user has table-level privileges for all parent tables,
     <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_no_referenced_row_2" target="_top">
      <code class="literal">
       ER_NO_REFERENCED_ROW_2
      </code>
     </a>
     and
     <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_row_is_referenced_2" target="_top">
      <code class="literal">
       ER_ROW_IS_REFERENCED_2
      </code>
     </a>
     error
            messages for foreign key operations expose information about
            parent tables. If a user does not have table-level
            privileges for all parent tables, more generic error
            messages are displayed instead
            (
     <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_no_referenced_row" target="_top">
      <code class="literal">
       ER_NO_REFERENCED_ROW
      </code>
     </a>
     and
     <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_row_is_referenced" target="_top">
      <code class="literal">
       ER_ROW_IS_REFERENCED
      </code>
     </a>
     ).
    </p>
    <p>
     An exception is that, for stored programs defined to execute
            with
     <code class="literal">
      DEFINER
     </code>
     privileges, the user against
            which privileges are assessed is the user in the program
     <code class="literal">
      DEFINER
     </code>
     clause, not the invoking user. If
            that user has table-level parent table privileges, parent
            table information is still displayed. In this case, it is
            the responsibility of the stored program creator to hide the
            information by including appropriate condition handlers.
    </p>
   </div>
  </div>
 </div>
 <br/>
</div>
