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