SQL State : 90057, Message : Constraint "PRIMARY KEY | UNIQUE (USERNAME)" not found; SQL statement:

Question

I am using h2 in memory DB, I have small spring boot application. When trying to run test I am getting error:

java.lang.IllegalStateException: Failed to load ApplicationContext Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration V1.0.1__create_authorities_table.sql failed ----------------------------------------------------- SQL State : 90057 Error Code : 90057 Message : Constraint "PRIMARY KEY | UNIQUE (USERNAME)" not found; SQL statement:

So it says that Flyway can't build it. I have only two sql files: V1.0.0__create_user_table.sql

CREATE TABLE users ( id IDENTITY PRIMARY KEY, username VARCHAR(50) NOT NULL, password VARCHAR(68) NOT NULL, enabled BOOLEAN NOT NULL );

and V1.0.1__create_authorities_table.sql:

CREATE TABLE authorities ( username VARCHAR(50) NOT NULL, authority VARCHAR(50) NOT NULL, FOREIGN KEY (username) REFERENCES users(username) ); CREATE UNIQUE INDEX ix_auth_username on authorities (username,authority);

I see that Flyway doesn't like FOREIGN KEY (username) REFERENCES users(username) so I also tried with ALTER TABLE authorities ADD FOREIGN KEY (username) REFERENCES users(username) but still doesn't work. I checked that when I just remove adding FOREIGN KEY then tests are green - no errors.

Seems like both ways from this: https://stackoverflow.com/a/41515905/4952262 are failing for me - what am I doing wrong here?

Evgenij Ryazanov · Accepted Answer · 2022-11-02 13:05:51Z

Referential constraints may reference only unique sets of columns, a primary key or unique constraint on them is required, so you need to create a unique constraint on users(username) to allow references to this column from referential constraints.

You can simply add an inline constraint to definition of this column:

CREATE TABLE users ( … username VARCHAR(50) NOT NULL UNIQUE, …

It will be better to remove username column from authorities table and add something like user_id BIGINT REFERENCES users(id) instead of it, however, duplication of long values between tables isn't a good practice.

Collectives™ on Stack Overflow

SQL State : 90057, Message : Constraint "PRIMARY KEY | UNIQUE (USERNAME)" not found; SQL statement:

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related