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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

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

Post 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)
Last edited by raghavan20 on Mon Jan 16, 2006 5:29 am, edited 1 time in total.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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)
Gambler
Forum Contributor
Posts: 246
Joined: Thu Dec 08, 2005 7:10 pm

Post 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?
Post Reply