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

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