Обсуждение: Using a serial primary key as a foreign key in a second table
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
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
> 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
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
Thanks for the help Sean. You're right about the problem you identified in my earlier reply concerning uniqueness, but fortunately the problem is that I picked a bad example---in an attempt to distill the essence of the problem I was was trying to solve from my own messy database I oversimplified. What I'm trying to do is model object inheritance in a relational database by defining 4 tables/ classes: 3 different types of children, each of which has its own table, and a parent class that's represented by one table which contains attributes common to all 3 child types. When I want to add an "object", I add a row to one of the three child tables (depending on the type of object I'm adding) and then add a record to the parent class table...so a single object is represented by a row in two different tables---I think it's called polymorphic association. I've been struggling to concisely find a way to add the child entry followed by the parent entry (which has to contain the ID of the child). So thanks again, especially for the link to the "INSERT" documentation. The line "If the INSERT command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) inserted by the command" seems to be new to the 8.2 docs---and I've spent hours pouring over the 8.1.4 manual! Nat ___________________________________________________________ All New Yahoo! Mail � Tired of Vi@gr@! come-ons? Let our SpamGuard protect you. http://uk.docs.yahoo.com/nowyoucan.html
On Thursday 21 December 2006 09:47, Nathaniel wrote: > Thanks for the help Sean. You're right about the problem you > identified in my earlier reply concerning uniqueness, but fortunately > the problem is that I picked a bad example---in an attempt to distill > the essence of the problem I was was trying to solve from my own > messy database I oversimplified. What I'm trying to do is model > object inheritance in a relational database by defining 4 tables/ > classes: 3 different types of children, each of which has its own > table, and a parent class that's represented by one table which > contains attributes common to all 3 child types. When I want to add > an "object", I add a row to one of the three child tables (depending > on the type of object I'm adding) and then add a record to the parent > class table...so a single object is represented by a row in two > different tables---I think it's called polymorphic association. I've > been struggling to concisely find a way to add the child entry > followed by the parent entry (which has to contain the ID of the child). Actually, your explanation of the problem is very succinct. Postgresql offers a direct solution to situations like these. See here: http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html Isn't postgresql an AWESOME product! > So thanks again, especially for the link to the "INSERT" > documentation. The line "If the INSERT command contains a RETURNING > clause, the result will be similar to that of a SELECT statement > containing the columns and values defined in the RETURNING list, > computed over the row(s) inserted by the command" seems to be new to > the 8.2 docs---and I've spent hours pouring over the 8.1.4 manual! It is a new feature in 8.2. I never asked what version of postgres you were using. However, my answer above is a better way to go about your specific problem, anyway. Sean
> Actually, your explanation of the problem is very succinct. Postgresql offers > a direct solution to situations like these. See here: > > http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html > > Isn't postgresql an AWESOME product! Not arguments here, but there is one point of caution with postgresql's table inheritance that should be considered in during schema design. You cannot use the parent's tables primary key as a foreign key in other parts of the schema, since the parent table does not >actually< have records inserted into the child tables. I have worked out an inheritance solution that does allow this just by using regular tables linked with foreign keys. To handle simultaneous insertion and updates across an entire branch of the hierarchy I used a view made updatable using the rule system. For deletes I just create the child tables using cascade deletes. The advantage using postgresql's tables inheritance is that it is quite easy to set up and it works very well. The solution I am using requires alot more planning and setup. :o( If any are interested I can provide more details on how this is accomplished. Regards, Richard Broersma Jr.
On Thu, Dec 21, 2006 at 12:07:35 +0000, Nathaniel <naptrel@yahoo.co.uk> wrote: > > 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. The historical way to do this was with nextval and currval. In 8.2 you also have the option of using the RETURNING clause. Looking at the documentation, it appears you can't directly use the output of the RETURNING clause in a subselect. You would need to save the returned value in your app or define a set returning function to do the company update.
Subject: Re: [NOVICE] Using a serial primary key as a foreign key in a second table When I want to add an "object", I add a row to one of the three child tables (depending on the type of object I'm adding) and then add a record to the parent class table... --------------------------------------------------- Nathaniel, is there some kind of constraint that absolutely requires you to enter the child record before the parent record? the customary way to do this is to add (or select) the parent, get its unique id, add the child(ren) and insert the parent'sid into the child's foreign key -> done. this is pretty simple and straightforward once you get the code nomenclaturedown. as Richard pointed out, once you start entering the child first, things get more complex. i'd recommend evaluating whetherthe "enter the child record first" constraint is really a required constraint. if not, drop it, enter the parentfirst, the child second and bask in the simplicity of the solution. if a "child first" constraint is absolutely required the, well, you gotta do what you gotta do. just make sure it *is* actuallyrequired before going through the hassle of "doing what ya gotta do." good luck. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> Nathaniel <naptrel@yahoo.co.uk> wrote: > 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. http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.2 4.15.2) How do I get the value of a SERIAL insert? One approach is to retrieve the next SERIAL value from the sequence object with the nextval() function before inserting and then insert it explicitly. Using the example table in 4.15.1, an example in a pseudo-language would look like this: new_id = execute("SELECT nextval('person_id_seq')"); execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')"); You would then also have the new value stored in new_id for use in other queries (e.g., as a foreign key to the person table). Note that the name of the automatically created SEQUENCE object will be named <table>_<serialcolumn>_seq, where table and serialcolumn are the names of your table and your SERIAL column, respectively. Alternatively, you could retrieve the assigned SERIAL value with the currval() function after it was inserted by default, e.g., execute("INSERT INTO person (name) VALUES ('Blaise Pascal')"); new_id = execute("SELECT currval('person_id_seq')"); Finally, you could use the OID returned from the INSERT statement to look up the default value, though this is probably the least portable approach. In Perl, using DBI with Edmund Mergl's DBD::Pg module, the oid value is made available via $sth->{pg_oid_status} after $sth->execute(). I am not sure if this is exactly what you might need. It also does not specify if this would cause problems for concurrent users or not. Hope it helps out though. Patrick