ERROR: null value in column "column_name" of relation "relation

I have this table:

DROP TABLE IF EXISTS mission CASCADE;

CREATE TABLE mission 
(
    id_mission SERIAL PRIMARY KEY,
    task_description VARCHAR(255) NOT NULL
);

And a mission can depend of another to be unlocked. So I have the next reflexive table:

DROP TABLE IF EXISTS depends CASCADE;

CREATE TABLE depends 
(
    id_mission_1 INTEGER,
    id_mission_2 INTEGER, -- it can be null (optional)
    FOREIGN KEY (id_mission_1) REFERENCES mission (id_mission),
    FOREIGN KEY (id_mission_2) REFERENCES mission (id_mission),
    PRIMARY KEY (id_mission_1, id_mission_2)
);

I'm importing the data from another table like this:

INSERT INTO depends(id_mission_1, id_mission_2)
    SELECT quest_id, quest_depends
    FROM player_quest;

The player_quest table looks like this:

We can see that not all the missions depend on one another, so there can be null values.

I'm getting the following error:

ERROR: null value in column "id_mission_2" of relation "depends" violates not-null constraint

Failing row contains (14, null).

How can I fix this?

Database: PostgreSQL

CodePudding user response：

Primary key columns can never be null. Use a UNIQUE constraint instead:

CREATE TABLE depends 
(
    id_mission_1 INTEGER,
    id_mission_2 INTEGER, -- it can be null (optional)
    FOREIGN KEY (id_mission_1) REFERENCES mission (id_mission),
    FOREIGN KEY (id_mission_2) REFERENCES mission (id_mission),
    UNIQUE (id_mission_1, id_mission_2)
);

Note that several (14, null) rows can be inserted!