jpa 相互依赖表

q35jwt9p  于 11个月前  发布在  其他
关注(0)|答案(1)|浏览(143)

我正在使用Java,Sping Boot ,JPA和H2数据库进行项目。然而,我对数据库表是如何设计的感到困惑。有3个表A,B和C。
表A将表B的主键作为外键。
表B将表C的主键作为外键。
由于每个表都依赖于其他表,所以当我创建所有三个表的模式时,我得到一个错误,指示找不到表B
我甚至尝试使用ALTER命令,但仍然面临同样的问题。
这是我的代码

DROP TABLE IF EXISTS TEAMS;
CREATE TABLE IF NOT EXISTS TEAMS (
  `team_id` int (11) NOT NULL AUTO_INCREMENT,
  `name` varchar (100) NOT NULL DEFAULT '0',
  `train_id` int (11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`team_id`),
  CONSTRAINT `FK_teams_trains` FOREIGN KEY (`train_id`) REFERENCES TRAINS (`train_id`)
);

DROP TABLE IF EXISTS USERS;
CREATE TABLE IF NOT EXISTS USERS (
  `user_id` int (11) NOT NULL AUTO_INCREMENT,
  `team_id` int (11) DEFAULT NULL,
  `reports_to` int (11) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `FK_users_teams` FOREIGN KEY (`team_id`) REFERENCES TEAMS (`team_id`),
  CONSTRAINT `FK_users_users` FOREIGN KEY (`reports_to`) REFERENCES USERS (`user_id`)
);

DROP TABLE IF EXISTS TRAINS;
CREATE TABLE IF NOT EXISTS TRAINS` (
  train_id INT (11) NOT NULL AUTO_INCREMENT,
  train_name varchar (100) NOT NULL DEFAULT '0',
  team_coach INT (11) NOT NULL DEFAULT '0',
  train_vp INT (11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`train_id`)
);

 ALTER TABLE TRAINS ADD CONSTRAINT `FK_trains_users` FOREIGN KEY (`team_coach`) REFERENCES USERS (`user_id`);
 ALTER TABLE TRAINS ADD CONSTRAINT `FK_trains_users_2` FOREIGN KEY (`train_vp`) REFERENCES USERS (`user_id`);
 ALTER TABLE TEAMS ADD CONSTRAINT `FK_teams_trains` FOREIGN KEY (`train_id`) REFERENCES TRAINS (`train_id`);
 ALTER TABLE USERS ADD CONSTRAINT `FK_users_teams` FOREIGN KEY (`team_id`) REFERENCES TEAMS (`team_id`);
 ALTER TABLE USERS ADD CONSTRAINT `FK_users_users` FOREIGN KEY (`reports_to`) REFERENCES USERS (`user_id`);

字符串
我该如何解决这个问题?

1cosmwyk

1cosmwyk1#

正如@Scary Wombat在评论中指出的那样,您可以从CREATE语句中删除CONSTRAINTS,并使用ALTER语句将其添加到末尾(正如您已经做的那样)
下面是更新后的SQL:

DROP TABLE IF EXISTS TEAMS;
CREATE TABLE IF NOT EXISTS TEAMS (
  `team_id` int (11) NOT NULL AUTO_INCREMENT,
  `name` varchar (100) NOT NULL DEFAULT '0',
  `train_id` int (11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`team_id`)
);

DROP TABLE IF EXISTS USERS;
CREATE TABLE IF NOT EXISTS USERS (
  `user_id` int (11) NOT NULL AUTO_INCREMENT,
  `team_id` int (11) DEFAULT NULL,
  `reports_to` int (11) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
);

DROP TABLE IF EXISTS TRAINS;
CREATE TABLE IF NOT EXISTS TRAINS (
  train_id INT (11) NOT NULL AUTO_INCREMENT,
  train_name varchar (100) NOT NULL DEFAULT '0',
  team_coach INT (11) NOT NULL DEFAULT '0',
  train_vp INT (11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`train_id`)
);

ALTER TABLE TRAINS ADD CONSTRAINT `FK_trains_users` FOREIGN KEY (`team_coach`) REFERENCES USERS (`user_id`);
ALTER TABLE TRAINS ADD CONSTRAINT `FK_trains_users_2` FOREIGN KEY (`train_vp`) REFERENCES USERS (`user_id`);
ALTER TABLE TEAMS ADD CONSTRAINT `FK_teams_trains` FOREIGN KEY (`train_id`) REFERENCES TRAINS (`train_id`);
ALTER TABLE USERS ADD CONSTRAINT `FK_users_teams` FOREIGN KEY (`team_id`) REFERENCES TEAMS (`team_id`);
ALTER TABLE USERS ADD CONSTRAINT `FK_users_users` FOREIGN KEY (`reports_to`) REFERENCES USERS (`user_id`);

字符串

相关问题