Thread: Inserting rows containing composite foreign keys

Inserting rows containing composite foreign keys

From

Nelson Green

Date:

26 November 2013, 01:01:25

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!

Re: Inserting rows containing composite foreign keys

From

Thomas Kellerer

Date:

26 November 2013, 10:16:00

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.

Re: Inserting rows containing composite foreign keys

From

Nelson Green

Date:

26 November 2013, 16:26:28

> To: pgsql-general@postgresql.org

> 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!