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

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: Using a serial primary key as a foreign key in a second table
Date: 2006-12-21 12:07:35
Message-ID: 866C2A2F-2992-4DEF-A0A2-CAC74CFEB6CD@yahoo.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
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?

That's all Folks,

Nat

	
	
		
___________________________________________________________ 
All new Yahoo! Mail "The new Interface is stunning in its simplicity and ease of use." - PC Magazine 
http://uk.docs.yahoo.com/nowyoucan.html

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2006-12-21 12:22:00
Subject: Re: Using a serial primary key as a foreign key in a second table
Previous:From: uldisDate: 2006-12-21 11:06:51
Subject: update order

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group