Skip site navigation (1) Skip section navigation (2)

Re: Using a serial primary key as a foreign key in a second table

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: pgsql-novice(at)postgresql(dot)org
Cc: Nathaniel <naptrel(at)yahoo(dot)co(dot)uk>
Subject: Re: Using a serial primary key as a foreign key in a second table
Date: 2006-12-21 13:38:21
Message-ID: 200612210838.21321.sdavis2@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
On Thursday 21 December 2006 08:04, Nathaniel wrote:
> > You simply add the company to the database, get its ID, and then
> > insert the
> > person with the appropriate company_id.  That is the simplest way
> > to think
> > about the process.  This will work for as many concurrent users as
> > you like.
>
> The problem here is that the company_id is the only field that is
> guaranteed to uniquely identify a company record: it's possible
> (albeit unlikely) that there is another "Looney Tunes" in the company
> table, but that one is the lesser-known Canadian company that
> manufactures bird whistles, and Bugs Bunny doesn't work for them. As
> a dumb human I can tell the difference (perhaps by looking at the
> company address field) but my clever computer is more persnickety. So
> I don't know how to identify the relevant record from which to "get
> its ID" without knowing its ID!
>
> Searching the internet, I found these two examples from an Oracle-
> related site and am looking to implement something analagous that
> works in postgres, but I'm new to PL/pgSQL and commands like
> "nextval" so am struggling.
>
>
> DECLARE
>    l_company_id  companies.company_id%TYPE;
> BEGIN
>    -- Select the next sequence value.
>    SELECT companies_seq.NEXTVAL
>    INTO   l_company_id
>    FROM   dual;
>
>    -- Use the value to populate the master table.
>    INSERT INTO companies (company_id, company_name)
>    VALUES (l_company_id, 'Looney Tunes');
>
>    -- Reuse the value to populate the FK link in the detail table.
>    INSERT INTO people (company_id, person_name)
>    VALUES (l_company_id, 'Bug Bunny');
>
>    COMMIT;
> END;
>
>
> DECLARE
>    l_company_id  companies.company_id%TYPE;
> BEGIN
>    -- Populate the master table, returning the sequence value.
>    INSERT INTO companies (company_id, company_name)
>    VALUES (companies_seq.NEXTVAL, 'Looney Tunes')
>    RETURNING company_id INTO l_company_id;
>
>    -- Use the returned value to populate the FK link in the detail
> table.
>    INSERT INTO people (company_id, person_name)
>    VALUES (l_company_id, 'Bugs Bunny');
>
>    COMMIT;
> END;
>
> Can anyone tell me how to translate either (I prefer the latter) into
> postgres-compliant SQL?

See here:

http://www.postgresql.org/docs/current/static/sql-insert.html

You can use this returned ID in your next insert.  Alternatively, you can 
select from the associated company sequence to get the "next" value for use 
in both insert statements (company and then person).  

The beginning of your email represents a larger problem, though.  Even though 
you have chosen an autoincrementing integer as your primary key, that doesn't 
guarantee uniqueness.  In fact, if you use your method of inserting a company 
and a person in the say shown in the above examply, you will end up with 
exactly as many company entries as person entries.  So, what you really want 
to do is to specify what makes a company unique (using a unique key 
constraint) so that you can look up a company by that unique key (can be 
combination of city, state, and name, for example).  IF and only if the 
company does not exist in your database do you do your insert.  Otherwise, 
you use the id from your lookup.  This is an important point in database 
design.  See here:

http://en.wikipedia.org/wiki/Database_normalization

Hope that helps.

Sean

In response to

Responses

pgsql-novice by date

Next:From: NathanielDate: 2006-12-21 14:47:38
Subject: Re: Using a serial primary key as a foreign key in a second table
Previous:From: NathanielDate: 2006-12-21 13:04:03
Subject: Re: Using a serial primary key as a foreign key in a second table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group