I have a MySQL table that looks like this:
I wanted to change such that the entry with id 15 (women, dress) now has a primary key of 8 and then the others are shifted by one, so for example Kids Tops now will be 9, etc. Is there an easy way to do this via phpmyadmin or a SQL query? Also because id 15 is already being used as a foreign key somewhere else, I wanted this change to be reflected all over the place in other tables.
You can modify the primary key of a table by changing the column order, index name, clustered option, or fill factor.
If you want to change the values in the column, then you have to first drop the primary key constraint. To change the value of the primary key, you have to drop the primary key constraint first.
Each table can only have one primary key. Access can automatically create a primary key field for you when you create a table, or you can specify the fields that you want to use as the primary key.
One should not change the primary key - ever. Moreover, it may be beneficial to think about PKs as non-numeric values. Imagine that you use autogenerated GUIDs for your primary keys.
If you want to renumber your items, then the column that you want to change should be a separate numeric column, which you treat explicitly as a sequence number of sorts. Then you can do what you want with three UPDATE
statements:
update mytable set sequence = -sequence where sequence = 15 -- hide the original 15
update mytable set sequence = sequence+1 where sequence >= 8
update mytable set sequence = 8 where sequence = -15
You cannot change a "primary key", and for good reason. The primary key is likely to be used by other tables to reference a particular row. So, a change would not be local in the table, it would have to be in every referencing table.
If you want to change the values in the column, then you have to first drop the primary key constraint.
If you really have to do such a thing, here are some considerations:
on delete cascade
).(I must admit that I might have missed something, because this is not something that I would ever do.)
Changing a primary key, especially one used in foreign key relationships, should not be taken lightly. The purpose of such keys is to maintain relational integrity. You should not be bothered by gaps in the key or lack of sequentiality. If you want a sequential number, you can add that into another column.
Perhaps you should alter your child table FK with an ON UPDATE CASCADE operation so that changes on the parent table get reflected on the child table.
See the MySQL reference for FK's: http://dev.mysql.com/doc/refman/5.1/en/create-table-foreign-keys.html
Later you can execute the following:
UPDATE table SET id = -15 WHERE id = 15;
UPDATE table SET id = id + 1 WHERE id >=8;
UPDATE table SET id = 8 WHERE id = -15;
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