CODEWITHSUNDEEP
javaspringpostgresqlspring-bootflyway
Nikolas
Nikolas

Reputation: 44398

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

I use Spring Boot and Flyway with this initialization script:

CREATE TABLE ADDRESS(
    ID bigserial NOT NULL PRIMARY KEY
                    );

CREATE TABLE ROLE(
    ID bigserial NOT NULL PRIMARY KEY
);

CREATE TABLE PERSON(
    ID bigserial NOT NULL PRIMARY KEY,
    FIRST_NAME VARCHAR(255),
    LAST_NAME VARCHAR(255),
    ADDRESS bigserial NOT NULL REFERENCES ADDRESS (ID),
    ROLE bigserial REFERENCES ROLE (ID)                  -- notice here is no 'not null'
);

All the relationship between the tables is that:

As soon as I start the application (I have also tried to post the query straight to the PostgreSQL database schema), there is somehow generated constraint not-null between the PERSON and ROLE tables.

Using DataGrip, I select SQL Scripts -> Generate DDL to Query Console and get the DDL for the tables (see below, new lines and roles definitions omitted for sake of brevity).

To my surprise, the NOT NULL is there although I haven't defined such constraint. How to get rid of it aside from altering table?

create table if not exists address
(
    id bigserial not null
        constraint address_pkey primary key
);

create table if not exists role
(
    id bigserial not nullconstraint role_pkey primary key
);

create table if not exists person
(
    id bigserial not null 
        constraint person_pkey primary key,
    first_name varchar(255),
    last_name varchar(255),
    address bigserial not null 
        constraint person_address_fkey references address,
    role bigserial not null                                   -- why is 'not null' here?
        constraint person_role_fkey references role
);

The version of PostgreSQL I use (through SELECT version()) is:

PostgreSQL 10.13, compiled by Visual C++ build 1800, 64-bit

Upvotes: 0

Views: 2648

Answers (2)

Yu Tian Toby
Yu Tian Toby

Reputation: 419

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

Upvotes: 2

sticky bit
sticky bit

Reputation: 37472

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

Upvotes: 3

Related Questions