Page 1 of 1

Unable to create a foreign key on existing table

Posted: Mon Jan 16, 2006 6:58 am
by raghavan20
There exists two tables, studentscores_tbl and subject_tbl
Their structures are,

Code: Select all

mysql> show create table studentscores_tbl;
-------------------------------------------------------------------------+
| Table             | Create Table
-------------------------------------------------------------------------+
| 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.09 sec)

mysql> show create table subject_tbl;
+-------------+-----------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------+
| Table       | Create Table
                                                                                       |
+-------------+-----------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------+
| subject_tbl | CREATE TABLE `subject_tbl` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` varchar(25) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+-----------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------+
Now, I am trying to reference id in subject_tbl from subjectid in studentscores_tbl. It is said in the manual, that foreign constraints cannot be created if index on the referencing field does not exist. But as you clearly see, a primary key index already exists.

Code: Select all

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