Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Sun Jan 15, 2006 8:12 am
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.
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Sun Jan 15, 2006 8:43 am
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 » Sun Jan 15, 2006 8:52 am
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?