Add two columns from a table as a foreign key in another

Question

I have students table as below

I want to create a new table certificates using SQL query where it will have certid as primary key auto incremented and rollno and marks should come from students table as foreign key (correct me if I am wrong) like below:

Possible duplicate of [Multiple-column foreign key in MySQL?](https://stackoverflow.com/questions/953035/multiple-column-foreign-key-in-mysql) — Robert Kock, Oct 11 '18 at 16:39
Can you post your certificates table columns and data? I think all you need is a simple join on rollNo whilst you will have to keep rollNo field as FK in certificate table — Pushpesh Kumar Rajwanshi, Oct 11 '18 at 16:45
@MadhurBhaiya I might be wrong in terms of sql stuff but I want both the data coming together. — Ankit4mjis, Oct 11 '18 at 16:49
@Ankit4mjis Why would `marks` be in `Students` table? What happen if a student takes more than one course? Which mark are you going to put in `Students` table? It doesn't make sense to put `marks` in `Students` table. — Eric, Oct 11 '18 at 17:04
@Eric consider that marks as full marks. and I only want to understand that my above requirement is possible or not. Or am I missing anything?? I'm a bit learning the SQL, so please don't mind it. — Ankit4mjis, Oct 11 '18 at 17:29

Pushpesh Kumar Rajwanshi · Accepted Answer · 2018-10-11T17:32:03.467

You have to create table certificates like this,

create table certificates (
    certId int auto_increment primary key,
    rollNo int,
    marks int,
    FOREIGN KEY (rollNo) REFERENCES students(rollNo)
);

Then using this command you can copy all data from students table to certificates table,

insert into certificates (rollNo,marks) select rollNo,marks from students;

Let me know if you needed this and have any issues doing it.

Add two columns from a table as a foreign key in another

1 Answers1