From: | Nathaniel <naptrel(at)yahoo(dot)co(dot)uk> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Using a serial primary key as a foreign key in a second table |
Date: | 2006-12-21 13:04:03 |
Message-ID: | B282AD73-1492-48B5-BC59-13C45EBD6304@yahoo.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2006-12-21 13:38:21 | Re: Using a serial primary key as a foreign key in a second table |
Previous Message | Sean Davis | 2006-12-21 12:22:00 | Re: Using a serial primary key as a foreign key in a second table |