Thursday, January 2, 2020

Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"; SQL statement:


When we try to connect to the embedded H2 database from H2 clients such as DBEver, I was getting following error message

Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"; SQL statement:
ALTER TABLE PUBLIC.IDN_OAUTH2_ACCESS_TOKEN_SCOPE ADD CONSTRAINT PUBLIC.CONSTRAINT_B1 FOREIGN KEY(TOKEN_ID) REFERENCES PUBLIC.IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE NOCHECK [23505-200]

When i search for this issue, It seems to be introduced with bug fixes for H2 database. Since in WSO2 we use H2 as the default database, we encountered this issue when connecting the H2 database.

So, we can avoid this issue by adding following part to the end of the database URL.


;mv_store=false

Once we added it , the full URL will look like follows.


jdbc:h2:/Users/shammijayasinghe/wso2am-2.6.0/repository/database/WSO2AM_DB;mv_store=false


Hope this helps.. Enjoy!!!

[1] https://groups.google.com/forum/#!topic/h2-database/5LZlZ_jC4QI