Why does the DBMS say that the primary key is not present in the table

I am a CS student that is taking his first course in databases so here I am using Postgresql to create a database with the following tables: employee, project, and worksOn.

So if you run the program you can see that both the employee and project table are created.

However, worksOn is not because it gives me the following error:

SQL Error [23503]: ERROR: insert or update on table “workson” violates foreign key constraint “fk_employee”
Detail: Key (essn)=(222443333) is not present in table “employee”.”

I know people won’t like it but if you could help that would be highly appreciated. Also, take in consideration that I never dealt with databases and I spent like a week on this.

DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS project;
DROP TABLE IF EXISTS worksOn;

create table employee(
    Ssn bigint generated always as identity,
    Fname varchar(45) not null,
    Lname varchar(45) not null,
    primary key(Ssn)
);

create table project(
    Pname varchar(45) not null,
    Plocation varchar(45) not null,
    Dnum int not null,  
    Pnumber int generated always as identity,
    primary key(Pnumber)
);

create table if not exists worksOn(
    Hours int not null,
    Essn bigint,
    Pno int,
--  index fk_emplyee,
    constraint fk_employee
        foreign key(Essn)
            references employee(Ssn)
            
--  constraint fk_project
--      foreign key(Pno)
--          references project(Pnumber)
--          on delete set null
);

insert into employee (Fname, Lname)
values('Jim', 'Brown');

--insert into project (Pname, Plocation, Dnum)
--values('ProjA', 'Boston', 3);

insert into worksOn (Essn, Pno, Hours)
values(222443333, 1, 20);
        

Answer

Your ssn column is defined as a identity column which means it’s generated automatically whenever you do not specify a value for it during insert. Given your SQL script the SSN value for Jim Brown will be 1, not 222443333 as you expected.

You need to remove the identity attribute from the column, then provide it during insert into the employee table:

create table employee(
    ssn bigint,  --<< no identity!
    fname varchar(45) not null,
    lname varchar(45) not null,
    primary key(ssn)
);
....
....
insert into employee (ssn, fname, lname)
values(222443333, 'Jim', 'Brown');

---

However, if you do want the SSN to be auto generated, you need to access the latest value when you insert into the workson table:

Assuming employee.ssn is still an identity column, you can use this:

insert into employee (fname, lname)
values('Jim', 'Brown');

insert into project (pname, plocation, dnum)
values('ProjA', 'Boston', 3);

insert into workson (essn, pno, hours)
values(
   currval(pg_get_serial_sequence('employee', 'ssn')), 
   currval(pg_get_serial_sequence('project', 'number')), 20
);