Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE project (
  project_id int NOT NULL GENERATED ALWAYS AS IDENTITY
, project text NOT NULL
, project_id_valid boolean NOT NULL DEFAULT true  -- NOT NULL!
, PRIMARY KEY(project_id, project_id_valid)  -- !
, CONSTRAINT project_id_always_valid CHECK (project_id_valid)  -- only true
);
COMMENT ON COLUMN project.project_id_valid IS 'Dummy column to allow conditional FK reference in table organization';

INSERT INTO project(project) VALUES ('project1');

CREATE TABLE organization (
  org_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, org text NOT NULL
, project_id integer NOT NULL
, project_id_valid boolean DEFAULT true  -- can be NULL!
, CONSTRAINT valid_project_fk FOREIGN KEY (project_id, project_id_valid) REFERENCES project -- !
, CONSTRAINT project_id_valid_or_null CHECK (project_id_valid)  -- only true
);
COMMENT ON COLUMN project.project_id_valid IS 'TRUE enforces the FK constraint valid_project_fk.';
1 rows affected

INSERT INTO organization(org, project_id) VALUES ('agency X', 1);
1 rows affected

-- invalid: no project_id = 2 in table project
INSERT INTO organization(org, project_id) VALUES ('agency Y', 2);
ERROR:  insert or update on table "organization" violates foreign key constraint "valid_project_fk"
DETAIL:  Key (project_id, project_id_valid)=(2, t) is not present in table "project".


-- disable FK to allow invalid project_id
INSERT INTO organization(org, project_id, project_id_valid) VALUES ('agency Y', 2, NULL);
1 rows affected

TABLE project;


project_id
project
project_id_valid


1
project1
t


TABLE organization;


org_id
org
project_id
project_id_valid


1
agency X
1
t

3
agency Y
2
null