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.