I just reviewed the MySQL manual page on partition restrictions, and found out that not only can you not have a foreign key on a partitioned table, you also cannot have a foreign key pointing to a partitioned table.

I am looking to partition a MySQL table by the month of the entry timestamp column (time that the row was inserted). As I understand from the mysql doc, a partitioning column should belongs to the primary key. Here are your options: 1. You can have a clustered index on FILE_UPLOADED_DATE and a separate, non-clustered, primary key on FILE_ID.In fact you already do something similar for the DocGUID column: The problem for me is that I want to change the PRIMARY_KEY to be a composite one ie PRIMARY KEY ('id','fk_id','begin') without changing the existing id columns (because it's a field that is used in the application to generate bookmarkable urls, so renumbering the ids is not an option) Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. You can fix … This also includes the table's primary key, since it is by definition a unique key. This is done by using PARTITION BY KEY, adding in CREATE TABLE STATEMENT. The server employs its own internal hashing function which is based on the same algorithm as PASSWORD(). how to partition a table in non primary key column (MYSQL) Ask Question Asked 5 years, 2 months ago. Also, please critique my “mysql partitioning” logic. I have to partition a table in MySQL, It has one primary key id. I have to partition by date field(non-primary,duplicate entries). 2. In KEY partitioning KEY takes only a list of zero or more column names. In mysql can I have a composite primary key composed of an auto increment and another field?

ALTER TABLE `products` DROP PRIMARY KEY , ADD PRIMARY KEY ( `id` , `category` ); ALTER TABLE `products` PARTITION BY KEY(category) PARTITIONS 6; Add auto_increment option to the id if you want it to be really unique, and don't specify the id value when you insert data in the table. Viewed 3k times 0. Learn more . Make client_id part of the table's primary key. "A PRIMARY KEY need to include all fields in the partition function." I have a table which contains 50GB data now I am trying to partition on the datetime column below are the details.
This is a blocker for many people who want to use table partitioning in MySQL. That means that the above isn't quite good enough. This means: Any column used in the partitioning expression must be part of the table's primary key, if it has a PK.
You are confusing the primary key and the clustered index. Active 4 years, 5 months ago. Don't use a primary key for this table. There is no reason for the two to be one and the same.

3. You can't always partition by the column you want to partition by, because it's either not in a unique key, or there is some other unique (or primary) key … Make client_id the table's primary key. MySQL KEY partition is a special form of HASH partition, where the hashing function for key partitioning is supplied by the MySQL server. To explain further-> I have a query about MySQL partition. 1.