MySQL Foreign Key ERROR. Constraint key error-CodePudding

I can't add a foreign key to an existing table. The error is as follows

Executing:
ALTER TABLE `sms`.`student_account` 
ADD INDEX `student_id_idx` (`student_id` ASC) VISIBLE;
;
ALTER TABLE `sms`.`student_account` 
ADD CONSTRAINT `student_id`
  FOREIGN KEY (`student_id`)
  REFERENCES `sms`.`student` (`student_id`)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2
SQL Statement:
ALTER TABLE `sms`.`student_account` 
ADD INDEX `student_id_idx` (`student_id` ASC) VISIBLE

CodePudding user response：

I've written the schema below which is working. I advise against using the same name student_id for the constraint as already used for the column. I would prefer fk_student_id to avoid risk of an ambiguity or conflict at some point.

create table `student_account` (
  student_id int);
create table  `student`(
  student_id int primary key
  );

✓

✓

ALTER TABLE `student_account` 
ADD INDEX `student_id_idx` (`student_id` ASC) VISIBLE;

✓

ALTER TABLE `student_account` 
ADD CONSTRAINT `student_id`
  FOREIGN KEY (`student_id`)
  REFERENCES `student` (`student_id`)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

✓

db<>fiddle here