The following is my table's create DDL:

create table REFDATA.CONFIG_COLLECTION  (
COLLECTION_ID        NUMBER(22,7)                      not null,
COLLECTION_CD        VARCHAR2(12)                    not null,
COLLECTION_TYPE      VARCHAR2(12)                    not null,
COLLECTION_DESC      VARCHAR2(255),
primary key (COLLECTION_ID) 
)
create unique index REFDATA.CONFIG_COLLECTION_AK on REFDATA.CONFIG_COLLECTION (COLLECTION_CD);

I then have the following alter to create a foreign key:

alter table REFDATA.CONFIG_COLLECTION_MEMBER
add foreign key (COLLECTION_CD)
references REFDATA.CONFIG_COLLECTION (COLLECTION_CD);

I get the following error when HSQLDB loads:

Caused by: java.sql.SQLException: Primary or unique constraint required on main table: CONFIG_COLLECTION in statement [

alter table REFDATA.CONFIG_COLLECTION_MEMBER
   add foreign key (COLLECTION_CD)
      references REFDATA.CONFIG_COLLECTION (COLLECTION_CD)]
    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.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:420)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
    ... 46 more

What is the problem here? It looks to me like I have the unique constraint correctly specified. This is version 1.8 of HSQLDB.

• hsqldb

A unique index is not a unique constraint. You don't need the unique index but must create a unique constraint.

ALTER TABLE REFDATA.CONFIG_COLLECTION ADD CONSTRAINT CONFIG_COLLECTION_AK UNIQUE(COLLECTION_CD);