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

[Nathaniel <naptrel(at)yahoo(dot)co(dot)uk>]

> 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