Unable to create a foreign key on existing table
Posted: Mon Jan 16, 2006 6:58 am
There exists two tables, studentscores_tbl and subject_tbl
Their structures are,
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.
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 |
+-------------+-----------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------+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)