Flyway and PostgreSQL nullable definition of foreign key still generates a non-null constraint

Solution 1:

"8.1.4. Serial Types":

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Note the NOT NULL.

Don't use bigserial for the foreign key. That doesn't make much sense. Simply use bigint.

CREATE TABLE IF NOT EXISTS person
                           (...
                            role bigint REFERENCES role);

Solution 2:

Possible solution 1: Changing Biserial to Bigint does not remove the null constraint set to foreign key column when running flyway in springboot to write into postgres DB (at least for my case)

• postgres:11.3-alphine 3.4
• flyway: 8.0.5

To be secure, need to add scripts to alter columns to be nullable

ALTER TABLE mytable ALTER COLUMN mycolumn DROP NOT NULL;

Change Postgres column to nullable

Possible solition 2: When Spring boot set JPA Hibernate ddl configuration to create, create-drop, update, flyway DB migration script will be updated by JPA entities properties. NOT NULL constraints can be added by JPA entities.

Change JPA Hibernate ddl configuration to none or validate will ensure only flyway script is used to create schema. JPA Hibernate ddl configuration