update foreign keys

axe · Post by **axe** » Thu 26 Mar 2009 08:48

if I try to update primary key, which also is foreign key for other table (in gui interface ),
then primary key are not updated. (all tables have InnoDB type)

but if I update table through sql query, then both fields update normal.

Duke · Post by **Duke** » Thu 26 Mar 2009 12:23

Could you please help us to reproduce the problem?
We need scripts of these tables and the query that you ran manually.

axe · Post by **axe** » Thu 26 Mar 2009 14:38

Code: Select all

CREATE TABLE `object` (
  `object_id` int(10) unsigned NOT NULL auto_increment,
  `obj_type` varchar(20) default NULL,
  `max_action` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`object_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

CREATE TABLE `attributes` (
  `attr_id` varchar(100) NOT NULL,
  `component_id` int(11) unsigned NOT NULL,
  `str_id` int(11) unsigned default NULL,
  `object_id` int(10) unsigned default NULL,
  `description` varchar(255) default NULL,
  PRIMARY KEY  (`attr_id`),
  KEY `fk_attributes__object_id` (`object_id`),
  CONSTRAINT `fk_attributes__object_id` FOREIGN KEY (`object_id`) REFERENCES `object` (`object_id`) ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8

no query. I can't change object.object_id in "edit table" mode

Duke · Post by **Duke** » Fri 27 Mar 2009 16:02

MySQL server does not allow renaming columns referenced in foreign key. You need to remove foreign key, change column and recreate foreign key.

axe · Post by **axe** » Mon 30 Mar 2009 05:55

I don't change column. I change some value in column. sql query "update" normally run. If I run query

Code: Select all

 update object set object_id=31 where object_id=33;

then object_id columns change in both tables. But "edit table" mode can't change the value.

Duke · Post by **Duke** » Mon 30 Mar 2009 14:39

Unfortunately, editing values of auto-increment column is not allowed from data editor. We are working over this problem.