Menu
I have encountered a problem in that I already have a composite primary key in a MYSQL table. But now I have added another column to that table and due to some requirement changes, I have to modify that composite primary key in such a way that I need to add that previously mentioned column to that composite primary key list. Can anyone tell me how to alter that table without dropping existing composite primary key. I am doing this in a Rails project
959
Oracle creates an index on the columns of a primary key; therefore, a composite primary key can contain a maximum of 16 columns. To define a composite primary key, you must use the table_constraint syntax rather than the column_constraint syntax.
Now, you can execute the ALTER TABLE statement to add a MySQL Composite Primary key as follows: mysql> alter table Orders ADD PRIMARY KEY (order_id, product_id);
Don't change your primary keys. Also don't use compound primary keys whenever possible. MySQL (and other databases) store the records in pages in PK order. MySQL will fill each page 15/16 full before allocating space for a new page, and inserting more data there.
You can't alter the primary key. You have to drop and re-add it:
ALTER TABLE MyTable DROP PRIMARY KEY, ADD PRIMARY KEY (old_col1, old_col2, new_col); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
