Reorder rows in database

Question

I need to change order of rows in database table. My table has 4 columns and 7 rows. I need to reorder these rows

pk_i_id int(10)    unsigned Auto Increment   
s_name             varchar(255) NULL     
s_heading          varchar(255) NULL     
s_order_type       varchar(10) NULL

In Adminer, when I've changed pk_i_id value(number) something else, I'm getting this error...

Cannot delete or update a parent row: a foreign key constraint fails    (`database_name`.`oc_t_item_custom_attr_categories`, CONSTRAINT `oc_t_item_custom_attr_categories_ibfk_1` FOREIGN KEY (`fk_i_group_id`) REFERENCES `oc_t_item_custom_attr_groups` (`pk_i_id`))

Do you know how to change it ? Thank you

Edit

oc_t_item_custom_attr_categories

 fk_i_group_id      int(10) unsigned     
 fk_i_category_id   int(10) unsigned    

indexes

PRIMARY fk_i_group_id, fk_i_category_id
INDEX   fk_i_category_id 

foregin keys

fk_i_group_id       oc_t_item_custom_attr_groups_2(pk_i_id) RESTRICT    RESTRICT    
fk_i_category_id    oc_t_category(pk_i_id)                  RESTRICT    RESTRICT    

Answer 1

You need to change your foreign key on table database_name.oc_t_item_custom_attr_categories so that it updates along with column it references.

ALTER TABLE database_name.oc_t_item_custom_attr_categories DROP CONSTRAINT oc_t_item_custom_attr_categories_ibfk_1;
ALTER TABLE database_name.oc_t_item_custom_attr_categories
  ADD CONSTRAINT oc_t_item_custom_attr_categories_ibfk_1 FOREIGN KEY (fk_i_group_id)
      REFERENCES oc_t_item_custom_attr_groups (pk_i_id)
      ON UPDATE CASCADE;

Since MariaDB seem to not support ADDING foreign keys after table creation, this is how it should work for you, assuming description of tables is correct:

RENAME TABLE oc_t_item_custom_attr_categories TO oc_t_item_custom_attr_categories_2;

CREATE TABLE oc_t_item_custom_attr_categories (
  fk_i_group_id int(10) unsigned,
  fk_i_category_id int(10) unsigned,
  PRIMARY KEY(fk_i_group_id, fk_i_category_id),
  INDEX (fk_i_category_id),
  CONSTRAINT `oc_t_item_custom_attr_categories_ibfk_1` FOREIGN KEY (fk_i_group_id)
      REFERENCES oc_t_item_custom_attr_groups (pk_i_id)
      ON UPDATE CASCADE,
  CONSTRAINT `oc_t_item_custom_attr_categories_ibfk_2` FOREIGN KEY (fk_i_category_id)
      REFERENCES oc_t_category (pk_i_id)
) ENGINE = XtraDB; --change engine to what you are using

INSERT INTO oc_t_item_custom_attr_categories SELECT * FROM oc_t_item_custom_attr_categories_2;

How it works on example data in MySQL database: http://rextester.com/ZAKR50399