ArrayIndexOutOfBoundException - hsqldb?

Question

I am currently using hsqldb. My create statements look like that:

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;

when I want to do:

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

I get:

However, the data exists in the other two tables therefore a reference should be possible?

Whats the problem here and how to solve it?

UPDATE:

version I use: 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)

UPDATE 2:

OK guys,

I tried a little and now I know that when I do not create the alter table I can insert the field easily...

Answer 1

This statement has an error and should be rejected by 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;

It gets accepted, but afterwards, when you try to insert values, the original error causes the exception (update: This issue has been fixed in HSQLDB 2.3.0 and it rejects the original statement).

You cannot have an FK on two columns referencing a PK that has only one column

You can add the ON DELETE CASCADE to the original CREATE TABLE

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)
);