svn commit - mysqldoc@docsrva: r1926 - trunk/refman-5.1

[jon]

Author: jstephens
Date: 2006-04-25 05:04:04 +0200 (Tue, 25 Apr 2006)
New Revision: 1926

Log:

Expanded explanation/example regarding primary/unique keys and partitioning 
keys.

(Fixes Docs Bug #19237)



Modified:
   trunk/refman-5.1/partitioning.xml

Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml	2006-04-25 02:25:21 UTC (rev 1925)
+++ trunk/refman-5.1/partitioning.xml	2006-04-25 03:04:04 UTC (rev 1926)
@@ -3905,8 +3905,33 @@
           expression value may also be <literal>NULL</literal>. (See
           <xref linkend="partitioning-handling-nulls"/>.)
         </para>
-
+        
         <para>
+          The one exception to this restriction occurs when partitioning
+          by <literal>KEY</literal>, where it is possible to use columns
+          of the <literal>CHAR</literal>, <literal>VARCHAR</literal>,
+          <literal>FLOAT</literal>, and <literal>DECIMAL</literal>
+          column types as partitioning keys, because MySQL's internal
+          key-hashing function produces the correct datatype from these.
+          For example, the following <literal>CREATE TABLE</literal>
+          statement is valid: 
+        </para>
+        
+<programlisting>
+CREATE TABLE tkc (c1 CHAR)
+    PARTITION BY KEY(c1)
+    PARTITIONS 4;
+</programlisting>
+        
+        <para>
+          This exception does <emphasis>not</emphasis> apply to
+          <literal>BLOB</literal> or <literal>TEXT</literal> column
+          types.
+        </para>
+      </listitem>
+      
+      <listitem>
+        <para>
           A partitioning key may not be a subquery, even if that
           subquery resolves to an integer value or
           <literal>NULL</literal>.
@@ -3972,10 +3997,49 @@
 </programlisting>
 
         <para>
-          If the table does not have a primary key, then this
-          restriction does not apply, and you may use any column or
-          columns in the partitioning expression.
+          If the table does not have a primary key or unique key, then
+          this restriction does not apply, and you may use any column or
+          columns in the partitioning expression. This also means that
+          you cannot later add a primary or unique key to a partitioned
+          table unless the primary key or unique key includes all
+          columns used for the partitioning key. Consider given the
+          partitioned table defined as shown here:
         </para>
+        
+<programlisting>
+CREATE TABLE t_no_pk (c1 INT, c2 INT) 
+    PARTITION BY RANGE(c1) (
+        PARTITION p0 VALUES LESS THAN (10), 
+        PARTITION p1 VALUES LESS THAN (20), 
+        PARTITION p2 VALUES LESS THAN (30), 
+        PARTITION p3 VALUES LESS THAN (40)
+    );
+</programlisting>
+        
+        <para>
+          It is possible to add a primary key to
+          <literal>t_no_pk</literal> using either of these 
+          <literal>ALTER TABLE</literal> statements: 
+        </para>
+        
+<programlisting>
+#  possible PK
+ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
+
+#  also a possible PK
+ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
+</programlisting>
+        
+        <para>
+          However, the next statement fails, because
+          <literal>c1</literal> is part of the partitioning key, but is
+          not part of the proposed primary key:
+        </para>
+        
+<programlisting>
+#  fails with ERROR 1482
+ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
+</programlisting>
       </listitem>
 
       <listitem>