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

CodePudding user response：

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.