conflicting NULL/NOT NULL declarations for column "id_bank_card" of table "bill"

Question

I Got a table BILL which is associated to another table BANK_CARD as follows:

create table BILL (
    id_bill              BIGSERIAL          not null,
    id_bank_card         BIGSERIAL          null,
    id_registred_user    BIGSERIAL          not null,
    reference_number     INT4                 null,
    purchase_date        DATE                 null,
    bill_status          VARCHAR(50)          null,
    payment_method       VARCHAR(50)          null,
    constraint PK_BILL primary key (id_bill)
);

create table BANK_CARD (
    id_bank_card         BIGSERIAL          not null,
    id_registred_user    BIGSERIAL          not null,
    card_type            VARCHAR(50)          null,
    card_number          INT4                 null,
    expiring_date        DATE                 null,
    cipher               INT4                 null,
    constraint PK_BANK_CARD primary key (id_bank_card)
);

The table BILL has a 0..1 association with the table BANK_CARD, and BANK_CARD has a 1..n association with table BILL.

But when i execute my sql script i get the following error:

conflicting NULL/NOT NULL declarations for column "id_bank_card" of table "bill"

Because the relationship BILL and BANK_CARD is 0..1 the foreign key id_bank_card can be null in the table bill, so i don't understand why i get this error.

Any help please? Thanks.

Answer 1

You are confusing data type definitions for primary and foreign keys. A bigserial is a generator of values of the bigint type. The foreign keys should use that data type. See table definitions below. Also, use of the NULL clause is redundant as that is the default behaviour. Primary keys can not be NULL so NOT NULL there is also redundant.

create table bank_card (
    id_bank_card         bigserial,
    id_registred_user    bigint references <user table>,
    card_type            VARCHAR(50),
    card_number          INT4,
    expiring_date        DATE,
    cipher               INT4,
    constraint PK_BANK_CARD primary key (id_bank_card)
);

create table bill (
    id_bill              BIGSERIAL,
    id_bank_card         bigint references bank_card,
    id_registred_user    bigint references <user table>,
    reference_number     INT4,
    purchase_date        DATE,
    bill_status          VARCHAR(50),
    payment_method       VARCHAR(50),
    constraint pk_bill primary key (id_bill)
);