Re: Inserting rows containing composite foreign keys

Nelson Green, 25.11.2013 23:01:
> Hello,
> 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');
>

Use an INSERT based on a SELECT, not based on VALUES:

INSERT INTO projects (fundsrc_number, project_seq, project_name, project_desc)
SELECT fundsrc_number, 1, 'proj1-1', 'first source01 project'
FROM fundsrc
WHERE fundsrc_name IN ('source01', 'source02');

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

Note that it's good coding style to always specify the columns in an INSERT statement.
It makes your statements more robust against changes.