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

> 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