<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="ansi-diff-foreign-keys">
      </a>
      1.7.2.3 FOREIGN KEY Constraint Differences
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045333254784">
  </a>
  <a class="indexterm" name="idm46045333253744">
  </a>
  <p>
   The MySQL implementation of foreign key constraints differs
          from the SQL standard in the following key respects:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If there are several rows in the parent table with the
              same referenced key value,
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      performs a foreign key
              check as if the other parent rows with the same key value
              do not exist. For example, if you define a
      <code class="literal">
       RESTRICT
      </code>
      type constraint, and there is
              a child row with several parent rows,
      <code class="literal">
       InnoDB
      </code>
      does not permit the deletion of
              any of the parent rows. This is shown in the following
              example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40707673"><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">CREATE</span> <span class="token keyword">TABLE</span> parent <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     id <span class="token datatype">INT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</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 output">Query OK, 0 rows affected (0.04 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> child <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     id <span class="token datatype">INT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     parent_id <span class="token datatype">INT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</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 prompt">    -&gt;</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 prompt">    -&gt;</span>         <span class="token keyword">REFERENCES</span> parent<span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">RESTRICT</span>
<span class="token prompt">    -&gt;</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 output">Query OK, 0 rows affected (0.02 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> parent <span class="token punctuation">(</span>id<span class="token punctuation">)</span> 
<span class="token prompt">    -&gt;</span>     <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 4 rows affected (0.01 sec)</span>
<span class="token output">Records: 4  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> child <span class="token punctuation">(</span>id<span class="token punctuation">,</span>parent_id<span class="token punctuation">)</span> 
<span class="token prompt">    -&gt;</span>     <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 3 rows affected (0.01 sec)</span>
<span class="token output">Records: 3  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> parent <span class="token keyword">WHERE</span> id<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1451 (23000)<span class="token punctuation">:</span> Cannot delete or update a parent row<span class="token punctuation">:</span> a foreign key
constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY
(`parent_id`) REFERENCES `parent` (`id`) ON DELETE RESTRICT)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      If
      <code class="literal">
       ON UPDATE CASCADE
      </code>
      or
      <code class="literal">
       ON
              UPDATE SET NULL
      </code>
      recurses to update the
      <span class="emphasis">
       <em>
        same table
       </em>
      </span>
      it has previously updated
              during the same cascade, it acts like
      <code class="literal">
       RESTRICT
      </code>
      . This means that you cannot
              use self-referential
      <code class="literal">
       ON UPDATE CASCADE
      </code>
      or
      <code class="literal">
       ON UPDATE SET NULL
      </code>
      operations. This
              is to prevent infinite loops resulting from cascaded
              updates. A self-referential
      <code class="literal">
       ON DELETE SET
              NULL
      </code>
      , on the other hand, is possible, as is a
              self-referential
      <code class="literal">
       ON DELETE CASCADE
      </code>
      .
              Cascading operations may not be nested more than 15 levels
              deep.
     </p>
    </li>
    <li class="listitem">
     <p>
      In an SQL statement that inserts, deletes, or updates many
              rows, foreign key constraints (like unique constraints)
              are checked row-by-row. When performing foreign key
              checks,
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      sets shared
              row-level locks on child or parent records that it must
              examine. MySQL checks foreign key constraints immediately;
              the check is not deferred to transaction commit. According
              to the SQL standard, the default behavior should be
              deferred checking. That is, constraints are only checked
              after the
      <span class="emphasis">
       <em>
        entire SQL statement
       </em>
      </span>
      has
              been processed. This means that it is not possible to
              delete a row that refers to itself using a foreign key.
     </p>
    </li>
    <li class="listitem">
     <p>
      No storage engine, including
      <code class="literal">
       InnoDB
      </code>
      ,
              recognizes or enforces the
      <code class="literal">
       MATCH
      </code>
      clause
              used in referential-integrity constraint definitions. Use
              of an explicit
      <code class="literal">
       MATCH
      </code>
      clause does not
              have the specified effect, and it causes
      <code class="literal">
       ON
              DELETE
      </code>
      and
      <code class="literal">
       ON UPDATE
      </code>
      clauses
              to be ignored. Specifying the
      <code class="literal">
       MATCH
      </code>
      should be avoided.
     </p>
     <p>
      The
      <code class="literal">
       MATCH
      </code>
      clause in the SQL standard
              controls how
      <code class="literal">
       NULL
      </code>
      values in a composite
              (multiple-column) foreign key are handled when comparing
              to a primary key in the referenced table. MySQL
              essentially implements the semantics defined by
      <code class="literal">
       MATCH SIMPLE
      </code>
      , which permits a foreign
              key to be all or partially
      <code class="literal">
       NULL
      </code>
      . In
              that case, a (child table) row containing such a foreign
              key can be inserted even though it does not match any row
              in the referenced (parent) table. (It is possible to
              implement other semantics using triggers.)
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <code class="literal">
       FOREIGN KEY
      </code>
      constraint that
              references a non-
      <code class="literal">
       UNIQUE
      </code>
      key is not
              standard SQL but rather an
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      extension that is now
              deprecated, and 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>
      .
              You should expect support for use of nonstandard keys to
              be removed in a future version of MySQL, and migrate away
              from them now.
     </p>
     <p>
      The
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      storage engine
              requires an explicit unique key (or primary key) on any
              column referenced as a foreign key, as per the SQL
              standard.
     </p>
    </li>
    <li class="listitem">
     <p>
      For storage engines that do not support foreign keys (such
              as
      <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
       <code class="literal">
        MyISAM
       </code>
      </a>
      ), MySQL Server
              parses and ignores foreign key specifications.
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL parses but ignores
      <span class="quote">
       “
       <span class="quote">
        inline
        <code class="literal">
         REFERENCES
        </code>
        specifications
       </span>
       ”
      </span>
      (as
              defined in the SQL standard) where the references are
              defined as part of the column specification. MySQL accepts
      <code class="literal">
       REFERENCES
      </code>
      clauses only when specified
              as part of a separate
      <code class="literal">
       FOREIGN KEY
      </code>
      specification.
     </p>
     <p>
      Defining a column to use a
      <code class="literal">
       REFERENCES
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       (
       <em class="replaceable">
        <code>
         col_name
        </code>
       </em>
       )
      </code>
      clause has no actual effect and
      <span class="emphasis">
       <em>
        serves only as a
              memo or comment to you that the column which you are
              currently defining is intended to refer to a column in
              another table
       </em>
      </span>
      . It is important to realize when
              using this syntax that:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         MySQL does not perform any sort of check to make sure
                  that
         <em class="replaceable">
          <code>
           col_name
          </code>
         </em>
         actually
                  exists in
         <em class="replaceable">
          <code>
           tbl_name
          </code>
         </em>
         (or even
                  that
         <em class="replaceable">
          <code>
           tbl_name
          </code>
         </em>
         itself
                  exists).
        </p>
       </li>
       <li class="listitem">
        <p>
         MySQL does not perform any sort of action on
         <em class="replaceable">
          <code>
           tbl_name
          </code>
         </em>
         such as deleting
                  rows in response to actions taken on rows in the table
                  which you are defining; in other words, this syntax
                  induces no
         <code class="literal">
          ON DELETE
         </code>
         or
         <code class="literal">
          ON
                  UPDATE
         </code>
         behavior whatsoever. (Although you
                  can write an
         <code class="literal">
          ON DELETE
         </code>
         or
         <code class="literal">
          ON UPDATE
         </code>
         clause as part of the
         <code class="literal">
          REFERENCES
         </code>
         clause, it is also
                  ignored.)
        </p>
       </li>
       <li class="listitem">
        <p>
         This syntax creates a
         <span class="emphasis">
          <em>
           column
          </em>
         </span>
         ; it
                  does
         <span class="bold">
          <strong>
           not
          </strong>
         </span>
         create any
                  sort of index or key.
        </p>
       </li>
      </ul>
     </div>
     <p>
      You can use a column so created as a join column, as shown
              here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99434239"><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> person <span class="token punctuation">(</span>
    id <span class="token datatype">SMALLINT</span> <span class="token keyword">UNSIGNED</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>
    <span class="token keyword">name</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">60</span><span class="token punctuation">)</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 punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> shirt <span class="token punctuation">(</span>
    id <span class="token datatype">SMALLINT</span> <span class="token keyword">UNSIGNED</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>
    style <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'t-shirt'</span><span class="token punctuation">,</span> <span class="token string">'polo'</span><span class="token punctuation">,</span> <span class="token string">'dress'</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    color <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'red'</span><span class="token punctuation">,</span> <span class="token string">'blue'</span><span class="token punctuation">,</span> <span class="token string">'orange'</span><span class="token punctuation">,</span> <span class="token string">'white'</span><span class="token punctuation">,</span> <span class="token string">'black'</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">owner</span> <span class="token datatype">SMALLINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">REFERENCES</span> person<span class="token punctuation">(</span>id<span class="token punctuation">)</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 punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> person <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 string">'Antonio Paz'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token variable">@last</span> <span class="token operator">:=</span> <span class="token function">LAST_INSERT_ID</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> shirt <span class="token keyword">VALUES</span>
    <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token string">'polo'</span><span class="token punctuation">,</span> <span class="token string">'blue'</span><span class="token punctuation">,</span> <span class="token variable">@last</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token string">'dress'</span><span class="token punctuation">,</span> <span class="token string">'white'</span><span class="token punctuation">,</span> <span class="token variable">@last</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token string">'t-shirt'</span><span class="token punctuation">,</span> <span class="token string">'blue'</span><span class="token punctuation">,</span> <span class="token variable">@last</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> person <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 string">'Lilliana Angelovska'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token variable">@last</span> <span class="token operator">:=</span> <span class="token function">LAST_INSERT_ID</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> shirt <span class="token keyword">VALUES</span>
    <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token string">'dress'</span><span class="token punctuation">,</span> <span class="token string">'orange'</span><span class="token punctuation">,</span> <span class="token variable">@last</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token string">'polo'</span><span class="token punctuation">,</span> <span class="token string">'red'</span><span class="token punctuation">,</span> <span class="token variable">@last</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token string">'dress'</span><span class="token punctuation">,</span> <span class="token string">'blue'</span><span class="token punctuation">,</span> <span class="token variable">@last</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token string">'t-shirt'</span><span class="token punctuation">,</span> <span class="token string">'white'</span><span class="token punctuation">,</span> <span class="token variable">@last</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> person<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>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> 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>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span> Antonio Paz         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  2 <span class="token punctuation">|</span> Lilliana Angelovska <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>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> shirt<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>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> style   <span class="token punctuation">|</span> color  <span class="token punctuation">|</span> owner <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>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span> polo    <span class="token punctuation">|</span> blue   <span class="token punctuation">|</span>     1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  2 <span class="token punctuation">|</span> dress   <span class="token punctuation">|</span> white  <span class="token punctuation">|</span>     1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  3 <span class="token punctuation">|</span> t<span class="token punctuation">-</span>shirt <span class="token punctuation">|</span> blue   <span class="token punctuation">|</span>     1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  4 <span class="token punctuation">|</span> dress   <span class="token punctuation">|</span> orange <span class="token punctuation">|</span>     2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span> polo    <span class="token punctuation">|</span> red    <span class="token punctuation">|</span>     2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span> dress   <span class="token punctuation">|</span> blue   <span class="token punctuation">|</span>     2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  7 <span class="token punctuation">|</span> t<span class="token punctuation">-</span>shirt <span class="token punctuation">|</span> white  <span class="token punctuation">|</span>     2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 keyword">SELECT</span> s<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> person p <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> shirt s
   <span class="token keyword">ON</span> s<span class="token punctuation">.</span><span class="token keyword">owner</span> <span class="token operator">=</span> p<span class="token punctuation">.</span>id
<span class="token keyword">WHERE</span> p<span class="token punctuation">.</span><span class="token keyword">name</span> <span class="token operator">LIKE</span> <span class="token string">'Lilliana%'</span>
   <span class="token operator">AND</span> s<span class="token punctuation">.</span>color <span class="token operator">&lt;&gt;</span> <span class="token string">'white'</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>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> style <span class="token punctuation">|</span> color  <span class="token punctuation">|</span> owner <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>
<span class="token output"><span class="token punctuation">|</span>  4 <span class="token punctuation">|</span> dress <span class="token punctuation">|</span> orange <span class="token punctuation">|</span>     2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span> polo  <span class="token punctuation">|</span> red    <span class="token punctuation">|</span>     2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span> dress <span class="token punctuation">|</span> blue   <span class="token punctuation">|</span>     2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
      When used in this fashion, the
      <code class="literal">
       REFERENCES
      </code>
      clause is not displayed in
              the output of
      <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>
      or
      <a class="link" href="describe.html" title="15.8.1 DESCRIBE Statement">
       <code class="literal">
        DESCRIBE
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa77859931"><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> shirt\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> shirt
Create Table<span class="token punctuation">:</span> CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   For information about foreign key constraints, see
   <a class="xref" href="create-table-foreign-keys.html" title="15.1.20.5 FOREIGN KEY Constraints">
    Section 15.1.20.5, “FOREIGN KEY Constraints”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
