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

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?

CodePudding user response：

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.