删除外键时的java问题

Question

我有两个表，定义如下；在用户表中，hospitalId和policlinikid都引用了表relhospol，如果从relhospol中删除了任何一行（如果有任何用户与之相关），我想将hospitalId和policlinikid设置为null，DDL会这样说。当我从SQLite管理器中删除一行时，它会将用户的hospitalId和PoliklinikId置为空，但是当我尝试从应用程序级别（Java）中删除一行时，它只从relhospol中删除，而不会将其设置为空（hospitalId，PoliklinikId）。缺少的点是什么
JDBC驱动程序：SQLite-JDBC-3.7.2

CREATE TABLE [USER] (
  [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [GROUPID] INTEGER CONSTRAINT [FK_USER_GID] REFERENCES [GROUP]([ID]) ON DELETE SET NULL ON UPDATE CASCADE, 
  [HOSPITALID] INTEGER, 
  [POLIKLINIKID] INTEGER, 
  [NAME] VARCHAR(50) NOT NULL, 
  [LOGINID] VARCHAR(15) NOT NULL, 
  [EMAIL] VARCHAR(50) NOT NULL, 
  [PASSWORD] VARCHAR(32) NOT NULL, 
  CONSTRAINT [FK_USER_RELHOSPOL] FOREIGN KEY([HOSPITALID], [POLIKLINIKID]) REFERENCES [RELHOSPOL]([HOSPITALID], [POLIKLINIKID]) ON DELETE SET NULL ON UPDATE CASCADE);

CREATE UNIQUE INDEX [AS] ON [USER] ([LOGINID]);
CREATE UNIQUE INDEX [AS1] ON [USER] ([EMAIL]));

CREATE TABLE [RELHOSPOL] (
  [HOSPITALID] INTEGER CONSTRAINT [FK_RELHOSPOL_HOS] REFERENCES [HOSPITAL]([ID]) ON DELETE SET NULL ON UPDATE CASCADE, 
  [POLIKLINIKID] INTEGER CONSTRAINT [FK_RELHOSPOL_POL] REFERENCES [POLIKLINIK]([ID]) ON DELETE SET NULL ON UPDATE CASCADE, 
  CONSTRAINT [sqlite_autoindex_RELHOSPOL_1] PRIMARY KEY ([HOSPITALID], [POLIKLINIKID]));

Answer 1

不能将主键设置为空：

CREATE TABLE [RELHOSPOL] (
[HOSPITALID] INTEGER CONSTRAINT [FK_RELHOSPOL_HOS] REFERENCES [HOSPITAL]([ID]) ON DELETE SET NULL ON UPDATE CASCADE, 
[POLIKLINIKID] INTEGER CONSTRAINT [FK_RELHOSPOL_POL] REFERENCES [POLIKLINIK]([ID]) ON DELETE SET NULL ON UPDATE CASCADE, 
CONSTRAINT [sqlite_autoindex_RELHOSPOL_1] PRIMARY KEY ([HOSPITALID], [POLIKLINIKID]));

您正在尝试从哪个表中删除一行？你能发布删除SQL吗

Answer 2

这只是一个猜测，但可能您没有意识到，您每次连接到数据库时都必须使用PRAGMA foreign_keys = ON;进行enable foreign key support？如果不这样做，您的语句将被解析，但不会强制执行。这可以解释为什么它在SQLite管理器中工作

您还应该知道，Sqlite的旧版本（<；3.6.19）可以容忍这种语法，但也不会强制执行任何内容

旁注： 当@Neuquino为表RELHOSPOL编写ON DELETE SET NULL语句时，似乎没有意义

Answer 3

好的，我改变了RELHOSPOL的定义如下；当我通过执行delete命令从SQL管理器的RELHOSPOL中删除行时，它会使用户表中必要的行为空。每当我使用下面所写的方法从应用程序中删除一行时，它只从RELHOSPOL中删除一行，而不会在USER表中将必要的行设置为NULL

CREATE TABLE [RELHOSPOL] (
  [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [HOSPITALID] INTEGER, 
  [POLIKLINIKID] INTEGER);

CREATE UNIQUE INDEX [UNIQUE_RELHOSPOL] ON [RELHOSPOL] ([HOSPITALID], [POLIKLINIKID]);

删除方法为：

public void deletePoliklinikFromHospital(int hospitalId, int poliklinikId) throws SQLException{
        String query = "DELETE FROM [RELHOSPOL] WHERE (HOSPITALID = ? AND POLIKLINIKID = ?)";
        try {
            PreparedStatement statement = db.prepareStatement(query);
            statement.setInt(1, hospitalId);
            statement.setInt(2, poliklinikId);
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        }
    }