Use composite primary key as a foreign key

Question

How can I use a composite primary key as a foreign key? It looks like my attempt is not working.

create table student ( student_id varchar (25) not null , student_name varchar (50) not null , student_pone int , student_CNIC varchar (50), students_Email varchar (50), srudents_address varchar(250), dept_id varchar(6), batch_id varchar(4), FOREIGN KEY (dept_id) REFERENCES department(dept_id), FOREIGN KEY (batch_id) REFERENCES batch(batch_id), CONSTRAINT pk_studentID PRIMARY KEY (batch_id,dept_id,student_id) ) 

---

 create table files ( files_name varchar(50) not null , files_path varchar(50), files_data varchar(max), files_bookmarks xml , FOREIGN KEY (pk_studentID ) REFERENCES student(pk_studentID ), CONSTRAINT pk_filesName PRIMARY KEY (files_name) ) 

Answer 1

Line:

 FOREIGN KEY (pk_studentID ) REFERENCES student(pk_studentID ), 

wrong. You cannot use pk_studentID like this, it is just the PK constraint name in the parent table. To use the composite primary key as a foreign key, you will need to add the same number of columns (that make up PK) with the same data types to the child table, and then use a combination of these columns in the FOREIGN KEY definition:

 CREATE TABLE files ( files_name varchar(50) NOT NULL, batch_id varchar(4) NOT NULL, --- added, these 3 should not dept_id varchar(6) NOT NULL, --- necessarily be NOT NULL student_id varchar (25) NOT NULL, --- files_path varchar(50), files_data varchar(max), --- varchar(max) ?? files_bookmarks xml, --- xml ?? --- your question is tagged MySQL, --- and not SQL-Server CONSTRAINT pk_filesName PRIMARY KEY (files_name), CONSTRAINT fk_student_files --- constraint name (optional) FOREIGN KEY (batch_id, dept_id, student_id) REFERENCES student (batch_id, dept_id, student_id) ) ENGINE = InnoDB ;