12/24/2022 0 Comments Mysql rename column![]() ![]() It seems that the "field" info does not contain a hint if it is part of a foreign key. There has never been an attempt to do any kind of "smart" alter column yet. I extended this function just to tell which indexes don't match between the two table definitions. This is based on the decision done by compare_tables(). It was my task to make "fast" add/drop index possible. Simple things like renaming the table or a column are seen as changes in the frm file only. This checks if alter table can be done without recreating the table. Jani created the function compare_tables(). Ingo Strüwing Just to clarify what has been done with "smart" alter table: Ingo and Jan worked on the coordination of intelligent ALTER TABLE in MySQL/InnoDB in spring 2006, but this was not completed. We must disable the intelligent ALTER TABLE until we have the internal RENAME COLUMN operation implemented inside InnoDB. Root page 3, appr.key vals 0, leaf pages 1, size pages 1įOREIGN KEY CONSTRAINT test/fk1: test/t2 ( col1 ) INDEX: name PRIMARY, id 0 504, fields 1/3, uniq 1, type 3 TABLE: name test/t1, id 0 352, columns 5, indexes 1, appr.rows 0ĬOLUMNS: col1: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4 prec 0 DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0 DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0 DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0 Your MySQL connection id is 2 to server version: 5.1.12-beta ![]() Heikki Tuuri Yes, column names go out-of-sync in. Mysql> alter table t1 change col1 col3 int ĮRROR 1025 (HY000): Error on rename of './test/#sql-652_1' to './test/t1' (errno: 150) Mysql> alter table t2 change col1 col2 int ĮRROR 1025 (HY000): Error on rename of './test/#sql-652_1' to './test/t2' (errno: 150) Mysql> create table t2 (col1 int primary key, constraint `fk1` foreign key (col1) Mysql> create table t1 (col1 int primary key) engine=innodb Ĭreate table t2 (col1 int primary key, constraint `fk1` foreign key (col1) Your MySQL connection id is 1 to server version: 5.0.25 There may be another bug associated with this: if MySQL does not tell InnoDB that the column name in the internal InnoDB data dictionary should be updated, then the table definition inside InnoDB will be out-of-sync with MySQL. That may explain why 5.1 does not give an error. Ingo has made ALTER TABLE more intelligent in 5.1. Heikki Tuuri In 5.0, InnoDB refuses to process the column rename. Renaming columns should result in foreign key definitions being updated appropriately. Mysql 5.1.11-beta (root) > insert into t1 values (1) ![]() Mysql 5.1.11-beta (root) > insert into t2 values (1) ĮRROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `fk1` FOREIGN KEY (`col1`) REFERENCES `t1` (`col1`)) The constraint is still checked, even though *neither* of the columns specified in the constraint definition exist: Mysql 5.1.11-beta (root) > alter table t1 change col1 col3 int Mysql 5.1.11-beta (root) > alter table t2 change col1 col2 int Mysql 5.1.11-beta (root) > show create table t2\G Mysql 5.1.11-beta (root) > create table t2 (col1 int primary key, constraint `fk1` foreign key (col1) references t1 (col1)) engine=innodb Mysql 5.1.11-beta (root) > create table t1 (col1 int primary key) engine=innodb This could mean that it could be impossible to reload from a dump, as creating constraints against non-existent columns would surely not work very well.Ĭreate table t1 (col1 int primary key) engine=innodb Ĭreate table t2 (col1 int primary key, constraint `fk1` foreign key (col1) references t1 (col1)) engine=innodb This occurs with both referenced and referencing tables. Renaming a column that appears in a Foreign Key definition does not update the Foriegn Key definition with the new column name. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |