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

Tag the question with proper DBMS . Most DBMSes support ON UPDATE CASCADE option in foreign key to propagate changes of PK to referencing tables. — Serg, Feb 10 '17 at 07:46

Łukasz Kamiński · Accepted Answer · 2017-02-10T12:58:40.160

2

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

edited Feb 10 '17 at 12:58

answered Feb 10 '17 at 07:47

Łukasz Kamiński

5,630
1
19
32

I've tryed execute your query and I've got this error >> Syntax error near 'CONSTRAINT oc_t_item_custom_attr_categories_ibfk_1' at line 1 – JZK Feb 10 '17 at 08:07
You would have to, like Serg asked earlier, tell us what DBMS you are using, so that we can figure out proper syntax. But in essence you need to alter your constraint so that ON UPDATE it will CASCADE, meaning it will change column that holds foreign key if referenced table changes. You can also drop temporarily constraint, make necessary updates on both tables and then recreate constraint. – Łukasz Kamiński Feb 10 '17 at 08:30
I'm using MariaDB and looking into it via Adminer. Please ask more specifically as I'm not sure what I should imagine under DBMS. Thank you (I'm newbie in databases) – JZK Feb 10 '17 at 08:34
I think that my syntax is correct, however it appears that MariaDB does not support adding constraints after table was created: https://mariadb.com/kb/en/mariadb/constraint/ So you would have to I suppose backup data, drop table and recreate it with updated constraint. You might also consider switching to MySQL if possible as it is supporting addition of constraints to already defined table and I personally find it unacceptable if MariaDB documentation is correct about not being able to do so in their DB. – Łukasz Kamiński Feb 10 '17 at 09:12
I see already. Do you think I can ask you for SQL query for such a job ? I don't think I can write it myself, unless I create new tables via user interface. And I'm not really motivated to spend hours on that) Thank you – JZK Feb 10 '17 at 10:10
You could rename your current table and then create new one using old name of table you just renamed: https://mariadb.com/kb/en/mariadb/rename-table/ If you provide structure of this table I can modify it by adding foreign key with cascade on update. – Łukasz Kamiński Feb 10 '17 at 10:20
I've renamed this table to "oc_t_item_custom_attr_groups_2" . Structure of that table is in my first post. If you need anything more, just ask – JZK Feb 10 '17 at 10:27
It is wrong table. The one with foreign key is: `oc_t_item_custom_attr_categories`. It references `oc_t_item_custom_attr_groups`, but this one doesn't need change in structure. – Łukasz Kamiński Feb 10 '17 at 10:29
I've updated my post with oc_t_item_custom_attr_categories table structure. Which table I should rename than? – JZK Feb 10 '17 at 10:39
I have updated my post with instruction for MariaDB. You should rename `oc_t_item_custom_attr_groups_2` back to what it was. – Łukasz Kamiński Feb 10 '17 at 10:58
I've renamed table, updated engine to InnoDB, executed command and got this error >> Syntax error near 'INSERT INTO oc_t_item_custom_attr_categories SELECT * FROM oc_t_item_custom_attr' at line 13 ... do you know what it should mean ? – JZK Feb 10 '17 at 11:11
It looks like error message is cut; is it full error output? – Łukasz Kamiński Feb 10 '17 at 11:15
Yes, first command was executed ok. Table was renamed. http://www.imagehosting.cz/?v=screentabl.png – JZK Feb 10 '17 at 11:20
You are missing semicolon at the end of create table (after `ENGINE = InnoDB`) – Łukasz Kamiński Feb 10 '17 at 11:24
Ah shit. Okay, now it worked out) So now, how I can reorder rows in oc_t_item_custom_attr_groups ? I've tried to change pk_i_id but got this error Cannot delete or update a parent row: a foreign key constraint fails (`database_name`.`oc_t_item_custom_attr_categories_2`, CONSTRAINT `oc_t_item_custom_attr_categories_2_ibfk_1` FOREIGN KEY (`fk_i_group_id`) REFERENCES `oc_t_item_custom_attr_groups` (`pk_i_id`)) – JZK Feb 10 '17 at 11:36
That foreign key now should have `ON UPDATE CASCADE` so it shouldn't throw errors when you are doing `UPDATE oc_t_item_custom_attr_groups SET pk_i_id = 32636245 WHERE pk_i_id = 5;`. Can you make sure it isn't set to `RESTRICT` anymore? – Łukasz Kamiński Feb 10 '17 at 12:32
yes, you are right. Table oc_t_item_custom_attr_categories has UPDATE CASCADE http://www.imagehosting.cz/?v=screenblb.png I've executed your query without errors, but still, I can reorder rows. In my opinion, when you look on error message pasted above, it still trying to do something with table oc_t_item_custom_attr_categories_2 ... which has UPDATE RESTRICTED. ... do you know how do deal with ? ... thank you! – JZK Feb 10 '17 at 14:57
ok, I was able to fix it by setting on every table which was mentioned in error message ON UPDATE CASCATE. Now it's working as expected. Thank you for your time and for pointing into right direction! :) – JZK Feb 10 '17 at 17:02

Reorder rows in database

1 Answers1