title: Mysql - how to keep unique constraint while partitioning by RANGE (timestamp)?
body: <p>I have one table, want to partition by RANGE (created_at timestamp), so can delete old data easily (by drop partition).</p>
  
  <pre><code>CREATE TABLE `orders` (
    `order_id` NVARCHAR(64) NOT NULL,
    `amount` INTEGER NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `modified_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `order_id` (`order_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  
  ALTER TABLE dropship.orders
  PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
      PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-03-01 00:00:00') ),
      PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-04-01 00:00:00') ),
      PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-05-01 00:00:00') ),
      PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-06-01 00:00:00') ),
      PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-07-01 00:00:00') ),
      PARTITION p5 VALUES LESS THAN (MAXVALUE)
  );
  </code></pre>
  
  <p>This table only has two usages: get by order_id, or update by order_id.</p>
  
  <pre><code>select * from orders where order_id = '123';
  update orders set amount = 10 where order_id = '123';
  </code></pre>
  
  <p>Due to the limitation of Mysql partitioning, I cannot add an unique key for order_id since will use created_at field for partitioning.</p>
  
  <pre><code>All columns used in the table's partitioning expression must be part of every unique key that the table may have, including any primary key.
  </code></pre>
  
  <p><strong>Question:</strong></p>
  
  <p>Any way to make order_id unique in this table please?</p>
  
  <p>I have thought about partitioning by order_id, but it's hard to delete old data in that way.</p>
  
  <p>Any suggestion is welcome. (For example may be you have better design for this table).</p>