• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to get primary key after doing an insert

 
Ranch Hand
Posts: 131
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello All

I have a table where I'm storing the main data I'm interested in
one of the fields is a zipcode
obviously this may be repeated so proper normalization suggests I should pull it out into another table
so now I have my main table which has a field that points to a table of zipcodes, and this other table with all the zipcodes
my question is, when I do an insert, I first insert the zipcode in it's table (if it's not already there)
a key get's auto-generated for it, which I need to insert into the main table
what's the proper way to do this??
what I want is to ask the database "if this zip code exists, give me it's key, if not, insert it and give me its key"
how do I do this??

using mysql

TIA

Dave
 
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
#1. If you are not doing any validation on the zip code or storing any dependent fields like city/state, you don't really need to move it to another table. Let it be an attribute of the table.

#2. There is a big debate about surrogate keys - Why won't you let the ZIP code be the primary key of the table zipcodes. It is a natural prinmary key ? Why generate an artificial key ?

#3. If you still want an auto-generated primary, I am assuming your table will have the columns SOME_PK, ZIP_CODE

so here is some sample code, take care to close your preparedstatements, resultsets in a finally block, and other exception handling code
 
Dave Robbins
Ranch Hand
Posts: 131
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi

I'm pretty much a rookie at DB design but I was under the impression it was considered good form to always have a row identifier that did nothing but identify the row, contained no further information
I recall an instance in the past of working with an existing database where failure to follow this principal caused a lot of problems, so I'm trying to avoid that (I don't really think it would be a problem in this case)

anyway
I see where you're going with this query

select SOME_PK from zip where ZIP_CODE = ?

but the "?" syntax doesn't seem to work for me
is it specific to a particular RDBMS?
I'm using MySQL

Thanks
Dave
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Dave Robbins:
I was under the impression it was considered good form to always have a row identifier that did nothing but identify the row, contained no further information



That is a good principle. What I queston is the need to move the zip code to another table.
 
Virag Saksena
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dave,
With good design, you should have a clearly identified natural primary key. However finding a good primary key (which will never change) is a non-trivial operation, so often people will use a generated key.

The advantage of using a generated ID is that you can change any of the columns without having to cascade the update to the children.

The disadvantage is performance : If you have ZIP code as a column in the original table (rather than foreign key) then queries like
zip code between 12345 and 12378
can be efficiently processed using an index.

The ? is the bind variable/query parameter. It does work in Oracle and postgreSQL. What exception are you getting ?
 
Dave Robbins
Ranch Hand
Posts: 131
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Bear,

the reason I think the zip needs to be placed in it's own table is because this is a web app and it's going to be collecting several different kinds of data from users, each of which will be "identified" by it's zipcode
heck, here's more info than you want to know
I keep bees
the app is going to collect info about when flowers bloom, when pollen starts to flow, when drones are first raised, etc
each of these events is tied to the location where it happens by zip code
I'm also going out to "wunderground.com" and collecting LOT'S of weather data about each zip code
the idea is to collate all these events to the weather in the year they occur and then try to predict when things are going to happen this year (beekeeping is all about "timing")
so, since several pieces of data are tied to the zipcode I think it deserves it's own table

Virag

the error I get is 1064 which appears to have something to do with ? being a reserved keyword
I think I'm just using it improperly
I'll fiddle around with it and see if I can get it to work

Dave
 
snakes are really good at eating slugs. And you wouldn't think it, but so are tiny ads:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic