java - 从联结表和关联表中删除的 SQL 查询

Question

对于事件和时间线，我有这么多的关系。在我的程序中，可以删除一个事件，然后我还需要从联结表中删除所有 this.eventID+timelineID 行。

这是我对这 3 个表的表结构：

CREATE TABLE `events` (
  `EventID` int NOT NULL AUTO_INCREMENT,
  `EventType` tinyint NOT NULL,
  `EventName` nvarchar(100) DEFAULT NULL,
  `EventDescription` nvarchar(5000) DEFAULT NULL,
  `StartYear` bigint NOT NULL,
  `StartMonth` tinyint unsigned NOT NULL,
  `StartDay` tinyint unsigned NOT NULL,
  `StartHour` tinyint unsigned  NULL,
  `StartMinute` tinyint unsigned  NULL,
  `StartSecond` tinyint unsigned  NULL,
  `StartMillisecond` smallint unsigned  NULL,
  `EndYear` bigint DEFAULT NULL,
  `EndMonth` tinyint unsigned DEFAULT NULL,
  `EndDay` tinyint unsigned DEFAULT NULL,
  `EndHour` tinyint unsigned DEFAULT NULL,
  `EndMinute` tinyint unsigned DEFAULT NULL,
  `EndSecond` tinyint unsigned DEFAULT NULL,
  `EndMillisecond` smallint unsigned DEFAULT NULL,
  `CreatedYear` bigint DEFAULT NULL,
  `CreatedMonth` tinyint unsigned DEFAULT NULL,
  `CreatedDay` tinyint unsigned DEFAULT NULL,
  `CreatedHour` tinyint unsigned DEFAULT NULL,
  `CreatedMinute` tinyint unsigned DEFAULT NULL,
  `CreatedSecond` tinyint unsigned DEFAULT NULL,
  `CreatedMillisecond` smallint unsigned DEFAULT NULL,
  PRIMARY KEY (`EventID`),
  UNIQUE KEY `EventID_UNIQUE` (`EventID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `timelines`
(
    `TimelineID`          int               NOT NULL AUTO_INCREMENT,
    `Scale`               nvarchar(100)     DEFAULT NULL,
    `TimelineName`        nvarchar(100)     DEFAULT NULL,
    `TimelineDescription` nvarchar(5000)    DEFAULT NULL,
    `Theme`               nvarchar(100)     DEFAULT NULL,
    `StartYear`           bigint            NOT NULL,
    `StartMonth`          tinyint unsigned  NOT NULL,
    `StartDay`            tinyint unsigned  NOT NULL,
    `StartHour`           tinyint unsigned  NOT NULL,
    `StartMinute`         tinyint unsigned  NOT NULL,
    `StartSecond`         tinyint unsigned  NOT NULL,
    `StartMillisecond`    smallint unsigned NOT NULL,
    `EndYear`             bigint            DEFAULT NULL,
    `EndMonth`            tinyint unsigned  DEFAULT NULL,
    `EndDay`              tinyint unsigned  DEFAULT NULL,
    `EndHour`             tinyint unsigned  DEFAULT NULL,
    `EndMinute`           tinyint unsigned  DEFAULT NULL,
    `EndSecond`           tinyint unsigned  DEFAULT NULL,
    `EndMillisecond`      smallint unsigned DEFAULT NULL,
    `CreatedYear`         bigint            DEFAULT NULL,
    `CreatedMonth`        tinyint unsigned  DEFAULT NULL,
    `CreatedDay`          tinyint unsigned  DEFAULT NULL,
    `CreatedHour`         tinyint unsigned  DEFAULT NULL,
    `CreatedMinute`       tinyint unsigned  DEFAULT NULL,
    `CreatedSecond`       tinyint unsigned  DEFAULT NULL,
    `CreatedMillisecond`  smallint unsigned DEFAULT NULL,
    `Private`             boolean           DEFAULT true,
    `TimelineOwner`       int,
    PRIMARY KEY (`TimelineID`),
    UNIQUE KEY `TimelineID_UNIQUE` (`TimelineID`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

 CREATE TABLE `timelineevents`
  (
      `TimelineID` int NOT NULL,
      `EventID` int NOT NULL,
      CONSTRAINT `pK_timelinesevent` PRIMARY KEY (eventID,timelineID),
      CONSTRAINT `fk_timelineevents_events1` FOREIGN KEY (`EventID`) REFERENCES `events` (`EventID`),
      CONSTRAINT `fk_timelineevents_timelines` FOREIGN KEY (`TimelineID`) REFERENCES `timelines` (`TimelineID`)
  ) ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_general_ci;

这是我试图构建的语句，以从事件表中删除事件以及在联结表“timelineevents”中与之相关的所有联结

 public PreparedStatement getDeleteQuery() throws SQLException {
        PreparedStatement out = DBM.conn.prepareStatement("DELETE FROM `events` WHERE (`EventID` = ?); DELETE FROM `timelineevents` WHERE (`fk_timelineevents_events1` = ?)");
        out.setInt(1, eventID);
        out.setInt(2, eventID);
        return out;
    }

这是正确的做法吗？

标签： javasqlforeign-keysmany-to-manysql-delete

Answer 1

不，在您的情况下，您应该使用两个 PreparedStatement 并在同一事务中执行它们。

或者，您可以将查询包装在存储过程或代码块中，并使用 CallableStatement 执行。