Postgres violates not null constraint, even when there isn't one

Question

Hey I have a Postgres database that has a Schema with

CREATE TABLE Mentor (
    mentor_ID serial unique,
    person_ID serial not null unique,
    career_history varchar(255) not null,
    preferred_communication varchar(50) not null,
    mentoring_preference varchar(50) not null,
    linked_in varchar(100) not null,
    capacity int not null,
    feedback_rating int,
    feeback_comment varchar(255),

    PRIMARY KEY (mentor_ID),
    CONSTRAINT fk_person FOREIGN KEY (person_ID) REFERENCES Person(person_ID)
);


CREATE TABLE Mentee(
    mentee_ID integer not null unique,
    mentor_ID serial references Mentor(mentor_ID),
    person_ID serial not null unique,
    study_year int,
    motivation varchar(50),
    interests varchar(255),
    random_match boolean default false,

    PRIMARY KEY (mentee_ID),
    CONSTRAINT fk_person FOREIGN KEY  (person_ID) REFERENCES Person(person_ID)
);

With this, i expect to be able to enter null values for mentor_ID in my database but when I enter the query

insert into mentee(mentee_ID, mentor_ID, person_ID) VALUES (12313, null, 1)

I get the violation

ERROR: null value in column "mentor_id" of relation "mentee" violates not-null constraint

I was wondering how I could make it so I can insert null values for mentor_ID? I dont have it as not null in the table but it still says violating not null constraint.

Thank you

`SERIAL` implies not null AFAIK. Being more familiar with MySQL I am inclined to say that using `SERIAL` is not appropriate for a foreign key as well but I am unfamiliar with (potential) postgres idioms — apokryfos, Sep 06 '22 at 05:53
Why do you expect that? You've marked it as the primary key. (Serial has nothing to do with it) — Richard Huxton, Sep 06 '22 at 05:53
@RichardHuxton I want to have it so Mentee's may have one or 0 mentors, , how would i reference the Mentor table from Mentee while also giving it the option to still have the entry as null? — othnuk, Sep 06 '22 at 06:03
`serial` is the outdated way of defining auto-increment columns. If you really want to stick to that (rather than using the standard-compliant `identity` columns), then **only** use it for primary key columns. The foreign key columns should just be `integer` — , Sep 06 '22 at 06:37
Don't use `serial` for a foreign key column. You don't want values to be generated automatically. — Laurenz Albe, Sep 06 '22 at 10:39

Schwern · Accepted Answer · 2022-09-06T15:38:46.827

Because serial is not null.

serial is...

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 integer not null. This is because serial is to be used for primary keys, not foreign keys. Foreign keys are always assigned, they don't need to auto increment.

Use a plain integer.

mentor_ID integer references Mentor(mentor_ID)

Same for your other foreign keys.

Notes:

identity is the SQL standard way to do auto incremented primary keys.
You don't need to declare primary keys as unique, primary keys are already unique.
Unless there's a specific reason to constrain the size of a text field, use text. varchar and text only use the necessary amount of space for each row. "foo" will take the same amount of space in varchar(10) as in varchar(255). For example, there's no particular reason to limit the size of their linked in nor motivation.

Thanks for the reply! So would I be able to reference it if i made mentor_ID into an integer that auto increments using identity and would it then be allowed to be null? — othnuk, Sep 06 '22 at 06:24
@othnuk Yes. All the foreign key needs to do is have the same type as what it's referencing: integer. It doesn't care what other constraints are on the primary key, just that the types match. — Schwern, Sep 06 '22 at 15:43

Postgres violates not null constraint, even when there isn't one

1 Answers1