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 12:22:00
Message-ID: 200612210722.00700.sdavis2@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
On Thursday 21 December 2006 07:07, Nathaniel wrote:
> Hello folks, I'm an SQL novice indeed, but working on the premise
> that "No question is too simple for this list" here goes:
>
> This is just a dummy example to make the simple problem I'm trying to
> solve as clear as possible. Let's say I have 2 tables: people and
> companies.
>
> Company records have a primary key "company_id" that is a serial, and
> some other fields such as the company's name.
>
> A person record is simply some text fields (like name) and a foreign
> key, "company_id" which ties the person to a company. There is a
> foreign key constraint on people that ensures that every person is
> tied to a company which exists in the database.
>
> I want to add a new person, "Bugs Bunny", but I know that the company
> he work for, "Looney Tunes", is not in the database.
>
> So, I want to add the company "Looney Tunes" (which is auto-assigned
> a company_id value), and then extract that id value so that I can use
> it in the company_id foreign key field of Bugs Bunny's person record.
>
> What's the simple, multiple concurrent users-safe way of doing this
> in postgreSQL?

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.

Sean

In response to

Responses

pgsql-novice by date

Next:From: NathanielDate: 2006-12-21 13:04:03
Subject: Re: Using a serial primary key as a foreign key in a second table
Previous:From: NathanielDate: 2006-12-21 12:07:35
Subject: 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