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)