Page 1 of 1

Unable to delete a column which is a primary key[unsolved]

Posted: Sun Jan 15, 2006 8:12 am
by raghavan20
1. You can see the error coming up when I try to delete the column which is a primary key.

Code: Select all

mysql> describe student_tbl;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     |      | PRI | NULL    | auto_increment |
| name  | varchar(35) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


mysql> alter table
    -> student_tbl
    -> drop column
    -> id;
ERROR 1025 (HY000): Error on rename of '.\test\#sql-68c_1fd' to '.\test\student_tbl' (err
no: 150)
2. I am not able to add foreign key after creating table

Code: Select all

mysql> describe studentscores_tbl;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11)          |      | PRI | NULL    | auto_increment |
| studentid | int(11)          |      | MUL | 0       |                |
| subjectid | int(10) unsigned |      |     | 0       |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


mysql> describe subject_tbl;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned |      | PRI | NULL    | auto_increment |
| name  | varchar(25)         | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)



mysql>  alter table
    ->  studentscores_tbl
    ->  add
    ->  foreign key (subjectid) references subject_tbl(id);
ERROR 1005 (HY000): Can't create table '.\test\#sql-68c_1fe.frm' (errno: 150)

Posted: Sun Jan 15, 2006 8:43 am
by raghavan20
I am posting all the problems in a single topic because all are related
Problem 3: Unable to drop a foreign key on a table

Code: Select all

mysql> show keys from studentscores_tbl;
+-------------------+------------+-----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
| Table             | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | C
ardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+-----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
| studentscores_tbl |          0 | PRIMARY   |            1 | id          | A         |
         1 |     NULL | NULL   |      | BTREE      |         |
| studentscores_tbl |          1 | studentid |            1 | studentid   | A         |
         1 |     NULL | NULL   |      | BTREE      |         |
+-------------------+------------+-----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> alter table studentscores_tbl
    -> drop
    -> foreign key studentid
    -> ;
ERROR 1025 (HY000): Error on rename of '.\test\studentscores_tbl' to '.\test\#sql2-68c-1f
e' (errno: 152)

Code: Select all

mysql> show create table studentscores_tbl;
| studentscores_tbl | CREATE TABLE `studentscores_tbl` (
  `id` int(11) NOT NULL auto_increment,
  `studentid` int(11) NOT NULL default '0',
  `subjectid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `studentid` (`studentid`),
  CONSTRAINT `studentscores_tbl_ibfk_1` FOREIGN KEY (`studentid`) REFERENCES `student_tbl
` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

mysql> alter table studentscores_tbl
    -> drop
    -> foreign key
    -> student_tbl_ibfk_1;
ERROR 1025 (HY000): Error on rename of '.\test\studentscores_tbl' to '.\test\#sql2-68c-1f
e' (errno: 152)

Posted: Sun Jan 15, 2006 8:52 am
by Gambler
http://bugs.mysql.com/bug.php?id=3491
it give the notorious error 150 not only when the detailed table index is
missing but also when the field is missing(misspelled), referred table is
missing or referred field is missing
So something have to be missing, I guess. *smiley* I would just dump structure, drop everuthing, modify dump, and then `source` it again. Is it acceptable solution?