List:Commits« Previous MessageNext Message »
From:jon Date:August 28 2006 9:14am
Subject:svn commit - mysqldoc@docsrva: r3103 - trunk/refman-5.1
View as plain text  
Author: jstephens
Date: 2006-08-28 11:14:08 +0200 (Mon, 28 Aug 2006)
New Revision: 3103

Log:
Revised partitioning limitations to make it clear that all unique keys must use all columns
from the partitioning expression (not just primary key)

Also noted that we hope to remove the limitation in a future version (request from Edwin,
verified by Mikael)

Modified:
   trunk/refman-5.1/partitioning.xml
   trunk/refman-5.1/sql-syntax.xml


Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml	2006-08-25 14:18:50 UTC (rev 3102)
+++ trunk/refman-5.1/partitioning.xml	2006-08-28 09:14:08 UTC (rev 3103)
Changed blocks: 8, Lines Added: 130, Lines Deleted: 66; 10219 bytes

@@ -4341,10 +4341,10 @@
 
         <para>
           The one exception to this restriction occurs when partitioning
-          by [<literal>LINEAR</literal>] <literal>KEY</literal>, where
-          it is possible to use columns of other types types as
-          partitioning keys, because MySQL's internal key-hashing
-          functions produce the correct datatype from these. For
+          by [<literal>LINEAR</literal>] <literal>KEY</literal> &mdash;
+          where it is possible to use columns of other types types as
+          partitioning keys &mdash; because MySQL's internal key-hashing          
+          functions produce the correct datatype from these types. For
           example, the following <literal>CREATE TABLE</literal>
           statement is valid:
         </para>

@@ -4376,15 +4376,26 @@
         </indexterm>
 
         <indexterm>
-          <primary>primary key</primary>
+          <primary>unique keys</primary>
           <secondary>and partitioning keys</secondary>
         </indexterm>
+        
+        <indexterm>
+          <primary>partitioning keys and unique keys</primary>
+        </indexterm>
 
-        <para id="partitioning-limitations-partitioning-keys-primary-keys">
-          If a table that is to be partitioned has a primary key, then
-          any columns used in the partitioning expression must be part
-          of the primary key. For example, the following two statements
-          are invalid:
+        <indexterm>
+          <primary>unique keys</primary>
+          <secondary>and partitioning keys</secondary>
+        </indexterm>
+
+        <para id="partitioning-limitations-partitioning-keys-unique-keys">
+          All columns used in the partitioning expression for a
+          partitioned table must be part of every unique key that the
+          table may have. In other words, every unique key on the table
+          must use every column in the tables partitioning expression.
+          For example, each of the following table creation statements
+          is invalid: 
         </para>
 
 <programlisting>

@@ -4393,27 +4404,50 @@
     col2 DATE NOT NULL,
     col3 INT NOT NULL,
     col4 INT NOT NULL,
-    PRIMARY KEY(col1, col2)
+    UNIQUE KEY (col1, col2)
 )
 PARTITION BY HASH(col3)
 PARTITIONS 4;
 
-CREATE TABLE t1 (
+CREATE TABLE t2 (
     col1 INT NOT NULL,
     col2 DATE NOT NULL,
     col3 INT NOT NULL,
     col4 INT NOT NULL,
-    PRIMARY KEY(col1, col3)
+    UNIQUE KEY (col1),
+    UNIQUE KEY (col3)
 )
-PARTITION BY HASH( YEAR(col2) )
+PARTITION BY HASH(col1 + col3)
 PARTITIONS 4;
+
+CREATE TABLE t3 (
+    col1 INT NOT NULL,
+    col2 DATE NOT NULL,
+    col3 INT NOT NULL,
+    col4 INT NOT NULL,
+    UNIQUE KEY (col1, col2),
+    UNIQUE KEY (col3)
+)
+PARTITION BY HASH(col1 + col3)
+PARTITIONS 4;
 </programlisting>
 
         <para>
-          In both cases, the primary key does not include all columns
-          referenced in the partitioning expression. However, both of
-          the next two statements are valid:
+          In each case, the proposed table would have at least one
+          unique key that does not include all columns used in the
+          partitioning expression.
         </para>
+        
+        <para>
+          Each of the following statements is valid, and represents one
+          way in which the corresponding invalid table creation
+          statement could be made to work:
+        </para>
+        
+        <remark remap="todo">
+          [js] Make one of these an interactive example showing the
+          error message once Bug #21862 has been fixed.
+        </remark>
 
 <programlisting>
 CREATE TABLE t1 (

@@ -4421,82 +4455,106 @@
     col2 DATE NOT NULL,
     col3 INT NOT NULL,
     col4 INT NOT NULL,
-    PRIMARY KEY(col1, col2)
+    UNIQUE KEY (col1, col2, col3)
 )
-PARTITION BY HASH(col1 + YEAR(col2))
+PARTITION BY HASH(col3)
 PARTITIONS 4;
 
-CREATE TABLE t1 (
+CREATE TABLE t2 (
     col1 INT NOT NULL,
     col2 DATE NOT NULL,
     col3 INT NOT NULL,
     col4 INT NOT NULL,
-    PRIMARY KEY(col1, col2, col4)
+    UNIQUE KEY (col1, col3)
 )
-PARTITION BY HASH(col1 + YEAR(col2))
+PARTITION BY HASH(col1 + col3)
 PARTITIONS 4;
+
+CREATE TABLE t3 (
+    col1 INT NOT NULL,
+    col2 DATE NOT NULL,
+    col3 INT NOT NULL,
+    col4 INT NOT NULL,
+    UNIQUE KEY (col1, col2, col3),
+    UNIQUE KEY (col3)
+)
+PARTITION BY HASH(col3)
+PARTITIONS 4;
 </programlisting>
-
-        <para>
-          This rule also applies with regard to any unique keys that the
-          table might have. For example, all of the following table
-          creation statements fail:
+                
+        <para> 
+          Since every primary key is by definition a unique key, this
+          restriction also includes the table's primary key, if it has
+          one. For example, the next two statements are invalid:
         </para>
 
 <programlisting>
-CREATE TABLE t1 (
+CREATE TABLE t4 (
     col1 INT NOT NULL,
     col2 DATE NOT NULL,
     col3 INT NOT NULL,
     col4 INT NOT NULL,
-    UNIQUE KEY(col1, col2)
+    PRIMARY KEY(col1, col2)
 )
 PARTITION BY HASH(col3)
 PARTITIONS 4;
 
-CREATE TABLE t1 (
+CREATE TABLE t5 (
     col1 INT NOT NULL,
     col2 DATE NOT NULL,
     col3 INT NOT NULL,
     col4 INT NOT NULL,
-    PRIMARY KEY(col1, col2),
-    UNIQUE KEY(col3)
+    PRIMARY KEY(col1, col3),
+    UNIQUE KEY(col2)
 )
-PARTITION BY HASH(col1 + col3)
+PARTITION BY HASH( YEAR(col2) )
 PARTITIONS 4;
+</programlisting>
 
-CREATE TABLE t1 (
+        <para>
+          In both cases, the primary key does not include all columns
+          referenced in the partitioning expression. However, both of
+          the next two statements are valid:
+        </para>
+
+<programlisting>
+CREATE TABLE t6 (
     col1 INT NOT NULL,
     col2 DATE NOT NULL,
     col3 INT NOT NULL,
     col4 INT NOT NULL,
-    PRIMARY KEY(col3),
-    UNIQUE KEY(col1, col2)
+    PRIMARY KEY(col1, col2)
 )
-PARTITION BY HASH(col3)
+PARTITION BY HASH(col1 + YEAR(col2))
 PARTITIONS 4;
+
+CREATE TABLE t7 (
+    col1 INT NOT NULL,
+    col2 DATE NOT NULL,
+    col3 INT NOT NULL,
+    col4 INT NOT NULL,
+    PRIMARY KEY(col1, col2, col4),
+    UNIQUE KEY(col2, col1)
+)
+PARTITION BY HASH(col1 + YEAR(col2))
+PARTITIONS 4;
 </programlisting>
 
         <para>
-          In each case, the table would have at least one primary key or
-          unique key that does not include all columns used in the
-          partitioning expression.
+          If a table has no unique keys &mdash; this includes having no 
+          primary key &mdash; then this restriction does not apply, and
+          you may use any column or columns in the partitioning
+          expression as long as the column type is compatible with the
+          partitioning type.
         </para>
 
         <para>
-          If the table does not have a primary key or any unique keys,
-          then this restriction does not apply, and you may use any
-          column or columns in the partitioning expression as long as
-          the column type is compatible with the partitioning type.
+          For the same reason, you cannot later add a unique key to a
+          partitioned table unless the key includes all columns used by
+          the table's partitioning expression. Consider given the
+          partitioned table defined as shown here:
         </para>
 
-        <para>
-          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) (

@@ -4526,6 +4584,11 @@
           <literal>c1</literal> is part of the partitioning key, but is
           not part of the proposed primary key:
         </para>
+        
+        <remark remap="todo">
+          [js] Make this an interactive example showing the error
+          message once Bug #21862 has been fixed.
+        </remark>
 
 <programlisting>
 #  fails with ERROR 1482

@@ -4559,8 +4622,13 @@
         <para>
           The following <literal>ALTER TABLE</literal> statements fails
           with an error, because the <literal>added</literal> column is
-          not part of the table's primary key:
+          not part of any unique key in the table:
         </para>
+        
+        <remark remap="todo">
+          [js] Make this an interactive example showing the error
+          message once Bug #21862 has been fixed.
+        </remark>
 
 <programlisting>
 ALTER TABLE np_pk

@@ -4569,18 +4637,9 @@
 </programlisting>
 
         <para>
-          If <literal>np_pk</literal> had a unique key defined on the
-          <literal>id</literal> column, then the previous statement
-          would fail since <literal>added</literal> would not be part of
-          that unique key.
+          This statement, however, would be valid:
         </para>
 
-        <para>
-          This statement, however, would be valid in either case
-          (<literal>PRIMARY KEY (id)</literal> or <literal>UNIQUE KEY
-          (id)</literal>):
-        </para>
-
 <programlisting>
 ALTER TABLE np_pk
     PARTITION BY HASH(id)

@@ -4591,11 +4650,16 @@
           In the case of <literal>np_pk</literal>, the only column that
           may be used as part of a partitioning expression is
           <literal>id</literal>; if you wish to partition this table
-          using any other columns for the partitioning key, you must
-          first modify the table's primary key, either by adding the
-          desired columns to the primary key, or by dropping the primary
-          key altogether.
+          using any other column or columns in the partitioning
+          expression, you must first modify the table, either by adding
+          the desired column or columns to the primary key, or by
+          dropping the primary key altogether.
         </para>
+        
+        <para>
+          We are working to remove this limitation in a future MySQL
+          release series.
+        </para>
       </listitem>
 
       <listitem>


Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml	2006-08-25 14:18:50 UTC (rev 3102)
+++ trunk/refman-5.1/sql-syntax.xml	2006-08-28 09:14:08 UTC (rev 3103)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 8; 1514 bytes

@@ -891,17 +891,16 @@
             ... PARTITION BY</literal> statement must follow the same
             rules as one created using <literal>CREATE TABLE ...
             PARTITION BY</literal>. This includes the rules governing
-            the relationship between a table's primary key, any unique
-            keys it might have, and the column or columns used in the
-            partitioning expression, as discussed in
-            <link linkend="partitioning-limitations-partitioning-keys-primary-keys">Partitioning
-            Limitations &mdash; Partitioning Keys and Primary
-            Keys</link>.
+            the relationship between any unique keys (including any primary
+            key) that the table might have, and the column or columns
+            used in the partitioning expression, as discussed in 
+            <link linkend="partitioning-limitations-partitioning-keys-unique-keys">Partitioning
+            Limitations: Partitioning Keys and Unique Keys</link>.
           </para>
 
           <para>
-            <literal>ALTER TABLE ... PARTITION BY</literal> was first
-            implemented in MySQL 5.1.6.
+            <literal>ALTER TABLE ... PARTITION BY</literal> became
+            available in MySQL 5.1.6.
           </para>
 
           <para>


Thread
svn commit - mysqldoc@docsrva: r3103 - trunk/refman-5.1jon28 Aug