Inserting rows containing composite foreign keys

Hello,

I have a question regarding inserting a row into a child table when the PK of the parent table is a composite key.

In this case, I have funding sources that buy projects, and projects generate jobs. Project numbers are not universally unique, but are unique to funding source. Therefore the PK of project entity is a composite of both the funding source FK and a sequentially incrementing number per funding source. The jobs table then has a PK that is an incrementing sequential number per the composite source/project FK:

CREATE TABLE fundsrc
(
fundsrc_number INTEGER,
fundsrc_name TEXT,
fundsrc_desc TEXT,
PRIMARY KEY (fundsrc_number)
);
INSERT INTO fundsrc
VALUES (1, 'source01', 'first source'), (2, 'source02', 'second source');

CREATE TABLE projects
(
fundsrc_number INTEGER,
project_seq INTEGER,
project_name TEXT,
project_desc TEXT,
CONSTRAINT fk_fundsrc FOREIGN KEY (fundsrc_number)
REFERENCES fundsrc(fundsrc_number),
PRIMARY KEY (fundsrc_number, project_seq)
);
CREATE INDEX project_id
ON projects(fundsrc_number, project_seq);
INSERT INTO projects
VALUES ((SELECT fundsrc_number FROM fundsrc
WHERE fundsrc_name = 'source01'),
1, 'proj1-1', 'first source01 project'),
((SELECT fundsrc_number FROM fundsrc

WHERE fundsrc_name = 'source02'),
1, 'proj2-1', 'first source02 project');

CREATE TABLE jobs
(
fundsrc_number INTEGER,
project_seq INTEGER,
job_seq INTEGER,
job_name TEXT,
job_desc TEXT,
CONSTRAINT fk_project FOREIGN KEY (fundsrc_number, project_seq)
REFERENCES projects(fundsrc_number, project_seq),
PRIMARY KEY (fundsrc_number, project_seq, job_seq)
);
CREATE INDEX job_id
ON jobs(fundsrc_number, project_seq, job_seq);

When inserting a record into the jobs table that references projects by name, do I have to query the projects table twice, once to get the funding source number, and once to get the project sequence number, even though both results will return the same row? Or put another way, is there a way to insert a row into the jobs table without having to perform two sub-queries for the same row, thus avoiding this:

INSERT INTO jobs
VALUES ((SELECT fundsrc_number FROM projects
WHERE project_name = 'proj1-1'),
(SELECT project_seq FROM projects
WHERE project_name = 'proj1-1'),
1, 'job1-1.1', 'first project 1-1 job');

Note that the name attributes are not candidate keys because they are to remain changeable.

This is not a terrible big deal, but I ask to satisfy my curiosity.

Thanks!