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

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

From: Nathaniel <naptrel(at)yahoo(dot)co(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Using a serial primary key as a foreign key in a second table
Date: 2006-12-21 13:04:03
Message-ID: B282AD73-1492-48B5-BC59-13C45EBD6304@yahoo.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
> 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?

Many thanks, Nat


		
___________________________________________________________ 
Yahoo! Messenger - with free PC-PC calling and photo sharing. http://uk.messenger.yahoo.com

In response to

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2006-12-21 13:38:21
Subject: Re: Using a serial primary key as a foreign key in a second table
Previous:From: Sean DavisDate: 2006-12-21 12:22:00
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