Re: Inserting rows containing composite foreign keys

> Date: Tue, 26 Nov 2013 08:15:45 +0100
>
> 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';

This works perfectly. I could swear I tried something similar and was unsuccessful, but I can't find any indication that I did in my history or my notes. Regardless, if you heard a loud smacking noise a few seconds ago that was my palm hitting my forehead. I appreciate you taking the time to state the obvious to the obviously blind.

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

I do. I just saved a few keystrokes and a bit of reading for this simple example. I know to specify and qualify at all times.

Thanks for both tips!