How to add three foreign key for same child table with three master tables?

[rushi3311]

Here i have four tables in my Database named "test_center" those are
1) "test_user" --> PK = u_id
2) "test_metadata" --> PK = test_id
3) "student_detail" --> PK = Student_id
4) "test_records" --> PK = test_record_id (Child table)

table 4 is child table and 1,2,3 are masters..! i am trying for "test_records" table to have three foreign key from each of these master table. But it is showing some errors as follows and screen shoots are attached too ! (I am using MySql workbench 5.0 and MySql server)

Error::

ERROR 1005: Can't create table 'test_center.#sql-aa4_12' (errno: 121)
SQL Statement:
ALTER TABLE `test_center`.`test_records` 
  ADD CONSTRAINT `Student_id`
  FOREIGN KEY (`Student_id` )
  REFERENCES `test_center`.`student_detail` (`Student_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `test_id`
  FOREIGN KEY (`test_id` )
  REFERENCES `test_center`.`test_metadata` (`test_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `u_id`
  FOREIGN KEY (`u_id` )
  REFERENCES `test_center`.`test_user` (`u_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE

ERROR: Error when running failback script. Details follow.
ERROR 1046: No database selected
SQL Statement: 
CREATE TABLE `test_records` (
  `test_record_id` int(11) NOT NULL AUTO_INCREMENT,
  `test_name` varchar(45) NOT NULL,
  `Result` float NOT NULL,
  `status` varchar(45) NOT NULL,
  `Student_id` varchar(45) NOT NULL,
  `u_id` int(11) NOT NULL,
  `test_id` int(11) NOT NULL,
  PRIMARY KEY (`test_record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

[smantscheff]

Show the output of
SHOW CREATE TABLE
for all involved tables (preferably as text, not as a screenshot).

[rushi3311]

| Table | Create Table
-------------------------------------------------------------------+
| test_user | CREATE TABLE `test_user` (
`u_id` int(11) NOT NULL AUTO_INCREMENT,
`u_name` varchar(45) NOT NULL,
`u_password` varchar(45) NOT NULL,
`u_level` varchar(45) NOT NULL,
PRIMARY KEY (`u_id`),
UNIQUE KEY `u_name_UNIQUE` (`u_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |

-------------------------------------------------------------------+
1 row in set (0.01 sec)

| Table | Create Table
-------------------------------------------+
| test_metadata | CREATE TABLE `test_metadata` (
`test_id` int(11) NOT NULL,
`test_name` varchar(45) NOT NULL,
`test_ques_num` int(11) NOT NULL,
`test_time` int(11) NOT NULL,
`examiner` varchar(45) NOT NULL,
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

-------------------------------------------+
1 row in set (0.00 sec)

| Table | Create Table
-------------------------------------------------------------------+
| Student_detail | CREATE TABLE `student_detail` (
`Student_id` varchar(45) NOT NULL,
`Student_Name` varchar(45) NOT NULL,
`Class` varchar(45) NOT NULL,
`Semester` varchar(45) NOT NULL,
`u_id` int(11) NOT NULL,
PRIMARY KEY (`Student_id`),
KEY `u_id` (`u_id`),
CONSTRAINT `u_id` FOREIGN KEY (`u_id`) REFERENCES `test_user` (`u_id`) ON DELE
TE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

----------------------+
1 row in set (0.01 sec)

| Table | Create Table
-------------------------------------------------------------------+
| test_records | CREATE TABLE `test_records` (
`test_record_id` int(11) NOT NULL AUTO_INCREMENT,
`test_name` varchar(45) NOT NULL,
`result` float DEFAULT NULL,
`status` varchar(45) DEFAULT NULL,
`Student_id` varchar(45) NOT NULL,
`u_id` int(11) NOT NULL,
`test_id` int(11) NOT NULL,
PRIMARY KEY (`test_record_id`),
KEY `Student_id` (`Student_id`),
CONSTRAINT `Student_id` FOREIGN KEY (`Student_id`) REFERENCES `student_detail`
(`Student_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
---------------------------------------------------------+
1 row in set (0.00 sec)

[smantscheff]

The following script does not generate any errors on my system.

use test;

drop table if exists test_records;
drop table if exists student_detail;
drop table if exists test_metadata;
drop table if exists test_user;

CREATE TABLE `test_user` (
`u_id` int(11) NOT NULL AUTO_INCREMENT,
`u_name` varchar(45) NOT NULL,
`u_password` varchar(45) NOT NULL,
`u_level` varchar(45) NOT NULL,
PRIMARY KEY (`u_id`),
UNIQUE KEY `u_name_UNIQUE` (`u_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
CREATE TABLE `test_metadata` (
`test_id` int(11) NOT NULL,
`test_name` varchar(45) NOT NULL,
`test_ques_num` int(11) NOT NULL,
`test_time` int(11) NOT NULL,
`examiner` varchar(45) NOT NULL,
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
CREATE TABLE `student_detail` (
`Student_id` varchar(45) NOT NULL,
`Student_Name` varchar(45) NOT NULL,
`Class` varchar(45) NOT NULL,
`Semester` varchar(45) NOT NULL,
`u_id` int(11) NOT NULL,
PRIMARY KEY (`Student_id`),
KEY `u_id` (`u_id`),
CONSTRAINT `u_id` FOREIGN KEY (`u_id`) REFERENCES `test_user` (`u_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
CREATE TABLE `test_records` (
`test_record_id` int(11) NOT NULL AUTO_INCREMENT,
`test_name` varchar(45) NOT NULL,
`result` float DEFAULT NULL,
`status` varchar(45) DEFAULT NULL,
`Student_id` varchar(45) NOT NULL,
`u_id` int(11) NOT NULL,
`test_id` int(11) NOT NULL,
PRIMARY KEY (`test_record_id`),
KEY `Student_id` (`Student_id`),
CONSTRAINT `xyz` FOREIGN KEY (`Student_id`) REFERENCES `student_detail`
(`Student_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

ALTER TABLE `test_records` 
  ADD CONSTRAINT `Student_id`
  FOREIGN KEY (`Student_id` )
  REFERENCES `student_detail` (`Student_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `test_id`
  FOREIGN KEY (`test_id` )
  REFERENCES `test_metadata` (`test_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE
;

[rushi3311]

Its not properly working....

U have set XYZ as Student_Id and secondly in altering table u have also added new foreign key for same column,.,..
n missed U_id column...for that...!

Can you correct it and repost it ???

As i dont know whats wrong with my MySql its not accepting any query edited by me...!
I have modified yours and tried to get it done with uid...!
But its not accepting....!
What can be the problm ????
Can you rectify that ???

[rushi3311]

I am trying this ....Still its not helping me...!

CREATE TABLE `test_record` (
`test_record_id` INT(11) NOT NULL AUTO_INCREMENT,
`test_name` VARCHAR(45) NOT NULL,
`result` FLOAT DEFAULT NULL,
`status` VARCHAR(45) DEFAULT NULL,
`Student_id` VARCHAR(45) NOT NULL,
`u_id` INT(11) NOT NULL,
`test_id` INT(11) NOT NULL,
PRIMARY KEY (`test_record_id`),
KEY `u_id` (`u_id`),
CONSTRAINT `u_id` FOREIGN KEY (`u_id`) REFERENCES `test_user`
(`u_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=latin1 ;
 
ALTER TABLE `test_records` 
  ADD CONSTRAINT `Student_id`
  FOREIGN KEY (`Student_id` )
  REFERENCES `student_detail` (`Student_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `test_id`
  FOREIGN KEY (`test_id` )
  REFERENCES `test_metadata` (`test_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE;

[smantscheff]

Show the complete script on which you are testing and the error messages of MySQL.
'xyz' ist just the name of a foreign key constraint. It does not matter how it is named as long as the name is unique.