MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.36


CREATE TABLE student
(
  student_id INTEGER AUTO_INCREMENT,
  student_name VARCHAR (50),
  CONSTRAINT student_pk PRIMARY KEY (student_id)
);

CREATE TABLE course
(
  course_id INTEGER AUTO_INCREMENT,
  course_year SMALLINT NOT NULL,
  course_name VARCHAR (100) NOT NULL,
  CONSTRAINT course_pk PRIMARY KEY (course_id)
);

CREATE TABLE registration
(
  reg_course_id INTEGER NOT NULL,
  reg_student_id INTEGER NOT NULL,

  CONSTRAINT reg_course_fk FOREIGN KEY (reg_course_id) REFERENCES course (course_id),
  CONSTRAINT reg_student_fk FOREIGN KEY (reg_student_id) REFERENCES student (student_id),
  CONSTRAINT registration_pk PRIMARY KEY (reg_course_id, reg_student_id),  
  CONSTRAINT registration_sc_uq  UNIQUE (reg_student_id, reg_course_id)
);