MySQL外键错误。约束密钥错误

发布于 2025-01-25 10:58:30 字数 604 浏览 3 评论 0原文

我无法在现有表中添加外键。错误如下

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

荒路情人 2025-02-01 10:58:30

我已经写了下面的模式。我建议不要使用相同的名称student_id作为已用于列的约束。我希望fk_student_id避免在某个时候遇到歧义或冲突的风险。

 创建表`sustome_account`（
  student_id int）;
创建表`''
  Student_id int主钥匙
  ）；

✓

✓

  alter表`sustome_account` 
添加索引`sustome_id_idx`（`sudents_id`ASC）可见;

✓

  alter表`sustome_account` 
添加约束“ student_id”
  外键（``sudent_id'）
  参考``学生'（``sudent _id''）
  在删除限制中
  在更新级联；

✓

db＆lt;＆gt;＆gt;

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;