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

Question

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:

Each PERSON has 0-1 ROLE. So, each ROLE belongs to 0-n PERSON. Hence, this relationship is nullable.
Each PERSON has 1 ADDRESS. So, each ADDRESS belongs to 1-n PERSON. Hence, this relationship is not-null.

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

Note that with Postgres 10 or later `identity` columns are recommended over `serial` anyway: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial — , Feb 10 '21 at 11:07

sticky bit · Accepted Answer · 2021-02-10T11:10:20.707

2

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

edited Feb 10 '21 at 11:10

answered Feb 10 '21 at 11:05

sticky bit

36,626
12
31
42

1

This is even more more obvious when using the (recommended) `identity` instead of `serial` – Feb 10 '21 at 11:12
Do I understand correctly that `bigserial` is good for the primary key definition as long as it specifies the autoincrement and `bigint` is appropriate for referencing such keys? – Nikolas Charalambidis Feb 10 '21 at 12:17
@NikolasCharalambidis: `bigint` is the underlying actual data type here. Using `bigserial` is also a `bigint`, it just adds some voodoo to automatically create unique values for the column using a sequence, like some macro that expands if you will. For primary keys that's convenient. But automatically creating values for a foreign key doesn't make much sense. They'd just more or less randomly reference a record (at least likely not the one you want) or occasionally generate a value not existing in the referenced table inducing an error. That's not so convenient. – sticky bit Feb 10 '21 at 12:39

Yu Tian Toby · Answer 2 · 2022-01-14T02:25:22.880

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

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

2 Answers2