ArrayIndexOutOfBoundException - hsqldb？

Question

我目前正在使用hsqldb。我的create语句如下所示：

CREATE TABLE Movie
(
    movieId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
);

CREATE TABLE Playlist(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
);

CREATE TABLE PlaylistMovies(
    mid INTEGER FOREIGN KEY REFERENCES Movie(movieid),
    pid INTEGER FOREIGN KEY REFERENCES Playlist(id),     
    PRIMARY KEY (mid, pid)
);
//my assoziation table which safes the movie ids and playlist ids


alter table playlistmovies
   add constraint fk_plm_playlist
   foreign key (pid, mid) references playlist(id)
   on delete cascade;

当我想做的时候：

INSERT INTO PlaylistMovies(MID, PID) VALUES (1,1);

我得到：

​

​

然而，数据存在于另外两个表中，因此应该可以引用？

这里的问题是什么，如何解决？

更新：

我使用的版本: hsqldb-2.2.8

java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: 1 java.lang.Arr
ayIndexOutOfBoundsException: 1
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
        at org.hsqldb.util.DatabaseManagerSwing.executeSQL(Unknown Source)
        at org.hsqldb.util.DatabaseManagerSwing.access$1000(Unknown Source)
        at org.hsqldb.util.DatabaseManagerSwing$StatementExecRunnable.run(Unknow
n Source)
        at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:251)
        at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:721)
        at java.awt.EventQueue.access$200(EventQueue.java:103)
        at java.awt.EventQueue$3.run(EventQueue.java:682)
        at java.awt.EventQueue$3.run(EventQueue.java:680)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDo
main.java:76)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:691)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThre
ad.java:244)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.
java:163)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThre
ad.java:151)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:147)

        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:139)

        at java.awt.EventDispatchThread.run(EventDispatchThread.java:97)
java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: 1 java.lang.Arr
ayIndexOutOfBoundsException: 1
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
        at org.hsqldb.util.DatabaseManagerSwing.executeSQL(Unknown Source)
        at org.hsqldb.util.DatabaseManagerSwing.access$1000(Unknown Source)
        at org.hsqldb.util.DatabaseManagerSwing$StatementExecRunnable.run(Unknow
n Source)
        at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:251)
        at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:721)
        at java.awt.EventQueue.access$200(EventQueue.java:103)
        at java.awt.EventQueue$3.run(EventQueue.java:682)
        at java.awt.EventQueue$3.run(EventQueue.java:680)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDo
main.java:76)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:691)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThre
ad.java:244)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.
java:163)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThre
ad.java:151)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:147)

        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:139)

        at java.awt.EventDispatchThread.run(EventDispatchThread.java:97)

更新2:

好的伙计们，

我试了一下，现在我知道当我不创建alter时，我可以很容易地插入字段.

Answer 1

此语句存在错误，应被HSQLDB拒绝。

alter table playlistmovies
add constraint fk_plm_playlist
foreign key (pid, mid) references playlist(id) -- mistake, FK has two columns, PK has one column
on delete cascade;

它被接受，但之后，当您尝试插入值时，原始错误将导致异常(update:此问题已在HSQLDB2.3.0中修复，并拒绝原始语句)。

不能在两个列上有一个FK引用只有一个列的PK

可以将ON DELETE级联添加到原始CREATE表中。

CREATE TABLE PlaylistMovies(
mid INTEGER FOREIGN KEY REFERENCES Movie(movieid) ON DELETE CASCADE,
pid INTEGER FOREIGN KEY REFERENCES Playlist(id) ON DELETE CASCADE,     
PRIMARY KEY (mid, pid)
);