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

Databases
1

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 screen shoots are attached too ! (I am using MySql workbench 5.0 and MySql server)


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
2

i did not understand your screenshots, but the code you posted is obviously wrong – you cannot ALTER a table before you CREATE it

3

I have already created that table…
now i am trying to add three foreign key …
n my gui based utility is not allowing me to do so… and throwing same error as i have mentioned above…!@

Can you write SQL for me to do so…!
As i dont know command well

4

what you posted in post #1 will do the job

5

Its not working for me…

As shown in figure i want to establish foreign key relationship for “test_records” table from “student_info” , “test_user” , “test_metadata”…!
I want field in “test_records” like Student_id , test_id , u_id should work as foreign key for table “test_records”…!

When i am trying to update whole table by using ALTER its showing some error that “table cant be created…!” I am not getting whats the actual problem is.?

6

try doing them one at a time…


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;
7

yA ITS WORKING MAN.,…!
tHANKSSS…!!!

iTS GREAT HELP …!

8 
ALTER TABLE test_center.test_records
ADD CONSTRAINT test_id
 FOREIGN KEY (test_id )
   REFERENCES test_center.test_metadata (test_id )
     ON DELETE CASCADE
     ON UPDATE CASCADE;

Whats error in this ???
M not getting it…!

9

…!