MySQL: Alter table to create partition giving primary key error
<p>I need to partition my MySQL table using LIST COLUMNS partition. I already have the table with data so I need to alter the table to create the partition.</p>

<p>Here is what I am doing.</p>

<p>Sample schema:</p>

<pre><code>CREATE TABLE mytable (
  id bigint(20) primary key auto_increment,
  .....
  status varchar(10)
);
</code></pre>

<p>Alter table script that I am using:</p>

<pre><code>ALTER TABLE mytable PARTITION BY LIST COLUMNS (status) 
( 
    PARTITION p1 VALUES IN (NULL), 
    PARTITION p2 VALUES IN ('SUCCESS'), 
    PARTITION p3 VALUES IN ('FAILED')
);
</code></pre>

<p><strong>Error: A PRIMARY KEY must include all columns in the table's partitioning function</strong></p>

<p>However, when I just tried to create a table without id column as below:</p>

<pre><code>CREATE TABLE mytable (
  status varchar(10)
);
</code></pre>

<p>And then try to create the partition using the same above alter script, it worked. So, am I missing anything on the table with id?</p>

<p><strong>SQLFiddle:</strong></p>

<p>Working: <a href="http://sqlfiddle.com/#!9/c8a3d1" rel="nofollow">http://sqlfiddle.com/#!9/c8a3d1</a></p>

<p>Not Working: <a href="http://sqlfiddle.com/#!9/d06a1" rel="nofollow">http://sqlfiddle.com/#!9/d06a1</a></p>