Inserting primary key for customer database.
The code below, Im using to insert a record into my access database called "db1". In access if I don't assign custID as the primary key I can insert as many records as I want, but then this fails the point of having custID field. If I assign custID as primary in access, then I can only insert one record and I get an error message "General Error". I tried having a simple increment, i, which would change the value of custID from 0 to 1, 2, etc. But this also fails giving the same error message. How do I overcome this? Is there a special function which takes care of primary keys? Anyone with any suggestion please reply!!
The action performed is from my GUI, it takes user input and creates a customer class and then sends it to add customer to be added to the customer database.
public Customer(int cid, String cName, String addr1, String addr2,
String cty, String pco, String t){
this.custID = cid;
this.custName = cName;
this.add1 = addr1;
this.add2 = addr2;
this.city = cty;
this.pcode = pco;
this.tel = t;
}
public String addCustomer(Customer c){
try{
//Gets the connection to customer database.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection myCon = DriverManager.getConnection("jdbc:odbc:db1");
Statement myStmt = myCon.createStatement();
//Does SQL insert in customer database.
String sqlQry ="INSERT INTO customer VALUES ('" + c.custID +"','"
+ c.custName +" ','" + c.add1 +"',' " + c.add2 +"','" + c.city
+"','" + c.pcode +"','" + c.tel +"')";
myStmt.executeUpdate(sqlQry);
//Closes connection.
myStmt.close();
myCon.close();
return"Insert Complete";
}
catch (Exception e){
return"Insert Failed: " + e;
}
}
publicvoid actionPerformed(ActionEvent e){
if (e.getSource() == added){
//Add Customer
int i = 0;
lblStatus.setText(cM.addCustomer(new Customer(i++,
txtName.getText(), txtAdd1.getText(), txtAdd2.getText(),
txtCity.getText(), txtPcode.getText(), txtTel.getText())));
}
elseif (e.getSource() == cls){
//Close 'Add Customer' window.
dispose();
}
}
[3574 byte] By [
SullyBluea] at [2007-9-30 2:23:32]
I don't know what you're seeing, but this isn't right.
If you've got a primary key field that's auto increment, you're not supposed to assign the primary key at all when you INSERT. The database takes care of that for you. If you try to manage it, you'll have problems - like you are now.
I recognize that constructor. That's part of the code I posted for you two days ago:
http://forum.java.sun.com/thread.jsp?thread=499108&forum=54&message=2357131
The stuff I gave you works. I tested it myself. It will insert all the records you want. Why did you change it? - MOD
If you don't have a custID field in the customer class, how do you later delete that particular record if you can't use your primary key field.
Say i had a record(000001, George, white house, washington, 0207859585). Now how would i then delete it using my primary key. considering there could be more than one george on the database.
You're correct, you DO need an ID field in the customer class, but you shouldn't be setting it. Your database does that.
What you REALLY need to do is INSERT the record, get the generated key value, and then set it in the Customer object. That's the common way of doing this. There are methods to get the last generated key in the java.sql package. Look at java.sql.Statement.RETURN_GENERATED_KEYS.
Too bad the JDBC-ODBC bridge driver won't support it.
In Oracle there's a specific query you can run to get the last generated key from a given sequence:
"SELECT " + sequenceName + ".CURRVAL FROM dual";
There's another for M$ SQL Server. There might be something like this for M$ Access. This is a common problem. I'd do a search on the forum to see how others recommend doing it for M$ Access.
Or learn how to use Hibernate, the object/relational mapping layer. - MOD
Cheers mate. I'll try that and let you know the outcome. Your help is much appreciated.
I have reading other questions in the forum and come to the conclusion there is no way to get the primary key from MS Access. Access will auto generate it for you but you can't get it back. the getGeneratedKey method doesn't work. Can anyone tell me a way of extracting the primary key from access!
> I have reading other questions in the forum and come
> to the conclusion there is no way to get the primary
> key from MS Access. Access will auto generate it for
> you but you can't get it back. the getGeneratedKey
> method doesn't work. Can anyone tell me a way of
> extracting the primary key from access!
i think there are three solutions to your problem.
1) change the primary key field from an autonumber field to a number field. i'm not sure why this isn't the case already really if you wanted to generate your own. if in your application you wanted to find the next available key you could run the following query in that instance.
String sqlQueryToGetHighestKeyValue = "SELECT MAX(cid) FROM customer;"
just replace cid with the actual field name of whatever the column is. then get that number add one to it and there's your next id
2) if you decide to keep the autonumber field the same query will give you the highest id, which in Access will be the last record you inserted. please keep in mind this trick may well not work for other types of databases but it should work for access.
3) the third solution is the most correct one from a relational theory point of view. make a primary key that is based on your data and not an arbitrary number. for example a primary key based on customer name and address fields would be correct in this instance i believe.
>
> 3) the third solution is the most correct one from a
> relational theory point of view. make a primary key
> that is based on your data and not an arbitrary
> number. for example a primary key based on customer
> name and address fields would be correct in this
> instance i believe.
>
I disagree with this statement. I thought that surrogate keys were a well-accepted practice, and keeping business logic out of primary keys was encouraged:
http://www.dbmsmag.com/9805d05.html
It's a staple of data warehousing. Today's "natural key" might not be what you want for tomorrow's design. Take scsi-boy's example. He wants to use name+address as the compound, natural key. It implies that name and address are columns in the same table.
What if you decide to normalize the database tomorrow, splitting all the address info off into a separate table and using a foreign key to join Party and Address tables together? A rational design, because you might find that a customer can be either an individual or a company, and there's now a 1:m relationship between your Party generalization and Address. Now you've got to redo all your keys. A surrogate key won't suffer from this problem.
I'm sure there's a debate among folks who are more knowledgable about relational databases than I. A data warehousing expert that I work with has told me that only neophytes look down on surrogate keys, so I'm following his advice. - MOD
Another way to approach it is to create your own sequence-like table in Access that will consist of a single auto-increment column. Every time you insert a record you'll get the current value of that column and use it as the primary key value. (Make sure that the tables that use it don't have their own auto increment columns - just ordinary number types.) It acts like the Oracle dual table and a sequence. - MOD
I misspoke about using Hibernate for this case. Gavin King and the Hibernate development team found Access to be so difficult to work with that they didn't include an implementation for it. Perhaps because of difficulties like this. - MOD
> >
> > 3) the third solution is the most correct one from
> a
> > relational theory point of view. make a primary key
> > that is based on your data and not an arbitrary
> > number. for example a primary key based on customer
> > name and address fields would be correct in this
> > instance i believe.
> >
>
> I disagree with this statement. I thought that
> surrogate keys were a well-accepted practice, and
> keeping business logic out of primary keys was
> encouraged:
>
> http://www.dbmsmag.com/9805d05.html
>
> It's a staple of data warehousing. Today's "natural
> key" might not be what you want for tomorrow's design.
just to raise a few counterpoints.
1) in response to keeping business logic out of primary keys was encouraged
i don't believe this is business logic problem but a data integrity problem and as such does belong in the database. given the following example.
custIDcustNameaddress
1 John Smith123 Anywhere St.
2 Joan Smith123 Anywhere St.
3 John Smith123 Anywhere St.
by using an arbitrary key we have allowed for duplicate records (tuples 1 and 3) my example would prevent this from happening.
2) in response to Today's "natural key" might not be what you want for tomorrow's design
your argument is with my choice of fields for the key... i agree this is a bad design, however, i went with the fields that were listed and this would not be my design. there are alternate designs that would be much better. for starters i think we can agree that storing the address with the customer information is probably unwise. addresses should be in another table that is related back to the customer table. what fields would i make a primary key out of in a table such as customer. a combination of name and email or birthdate etc. would very likely be appropriate.
3) in response to only neophytes look down on surrogate keys
i think this advice can be very dangerous in practice. first i agree that using surrogate keys can make some things easier... i use them myself... but when i do i also make sure that there is an additional unique index on the fields that make up the "real" key. in practice i have dealt with many, many databases that lack data integrity because they had auto-incrementing primary key fields slapped onto tables by people who didn't understand the consequences of their actions.
4) sorry but... i really have a problem with the keeping business logic out of primary key statement. a primary key is supposed to help with maintaining data integrity. as a real world example here i am going to describe a project i am working on currently.
the system keeps track of electricty meter readings for buildings.
three tables
meter which has a buinch of fields but the two keys are a primary key (meter serial number) and a foriegn key (id of the building it's in)
building (primary key is a surrogate key id but there is also a unique index on address and postal code)
meterreading three fields (the primary key is on two of them, meterserialnumber and datetime of reading)
as part of the project i migrated the data from Access to SQL-Server, in the old system the meterreading table's primary key was an autonumber field. and guess what... there were all kinds of duplicate records.
the problem with saying that was a business logic problem is that there are several ways data is entered into the system and i think having the intergrity of your data relying on all the programs that use the system to be aware of what the constraints should be is a dangerous practice.
my point is that as i have hopefully given here sometimes using surrogate keys is appropriate and sometimes it isn't and even when it is you really must account for it elsewhere (usually by creating another index) otherwise in practice you are setting a big potential for creating a mess of data.
and for the record this based on my understanding of relational theory and my experience in practice with many databases so i wouldn't describe myself as a neophyte.
> http://www.dbmsmag.com/9805d05.html
btw the problems and issues discussed in that article deal specifically with data warehousing which has it's own unique set of issues when compared to a production database.
as a generalization in a data warehouse the performance for analyzing data takes precedence over the intergrity of the data in the system again when compared with what matters in a production database.
further if the data being loaded into the warehouse is coming from a production database that has very strong intergrity than the design of the warehouse data can be optimized for perfomance (using surrogate keys) and not worry at all about the integrity of the data since this has already been determined.
most of the problems in the article associated with natural keys are in fact a product of replication. in the past, pretty much the only way to deal with this problem was to us a surrogate key, however i believe that many databases now support the concept of using natural keys for replication.
in summary the article makes some convincing arguments for using surrogate keys in a warehousing environment but i remain wholly unconvinced that this means it is suitable solution for all environments, particularly non-warehousing ones.
>
> i think this advice can be very dangerous in
> practice. first i agree that using surrogate keys can
> make some things easier... i use them myself... but
> when i do i also make sure that there is an additional
> unique index on the fields that make up the "real"
> key. in practice i have dealt with many, many
> databases that lack data integrity because they had
> auto-incrementing primary key fields slapped onto
> tables by people who didn't understand the
> consequences of their actions.
>
I agree with this. I just finished a problem where I had surrogate keys for each table AND unique contraints where appropriate on alternate keys. Using surrogate keys without adding unique indexes when required isn't a good idea.
It's not to say that there aren't alternative keys possible and other relationships to be maintained. It's just to say that keeping business logic out of keys has advantages.
>
> and for the record this based on my understanding of relational theory and my experience in practice with
> many databases so i wouldn't describe myself as a neophyte.
>
Your discussion is proof that you're not. The expert that I'm quoting has demonstrated his ability to me, so I have faith in what he's telling me. The kind of folks he's referring to zs database neophytes are VB programmers who see no reason to even have the discussion. - MOD
I feel like adding my 2c worth to this so here goes...
I'd just like to say that I'm a fan of surrogate keys, but I don't make it a rule to use a surrogate key on every table. I generally make a judgement based on the meaning of the data and it's natural key in the contact of it's usage. Typically, this means that if the natural key has some "meaning" then a surrogate key may be advisable.
For example, in a list of inventory items, the barcode would be a bad choice for a primary key, because even though they are unique in the database at any one time (for active items) they do get recycled, so in a space of two years the same barcode could refer to different items in history.
On the other hand, I like to use abbreviated codes (as opposed to integers) for simple lookups as it makes it easier to debug data, and the codes typically have no meaning of themselves.
In the end it's professional choice (as opposed to personal choice?) If we are professionals we will document our standards and follow them consistently. That is far better than building systems with a hotch potch of ideas based on the various prjudices of the individuals involved in the project.
Regarding the problem of getting the value of an auto-generated sequence after inserting a row(the original topic I think it was?)...
I cannot recommend selecting currval from the sequence to find the last value (oracle case, but it applies to all dbs that have such sequences) as sequences are not a part of any transaction, this will fail at somepoint in a multi user application, as it may not be the last value given to you, but the last value given to someone else.
As I understand it, you have the following options (in order of decreasing desirability):
1. re-fetch the row from the result set. The RS must be updateable (and still open) to do this.
2. re-read the row using an alternate (or natural) key to get the autogenerated surrogate key.
3. discard the result set and issue the select again to build it afresh.
Instead of using an auto-generated sequence, you could select the next sequence number from the sequence prior to each insert, but it's a little extra overhead.
Don't be tempted to select the next sequence value once and incerement it locally if your application is multi user. Best to select the next sequence value each time.
Hoping this helps *somebody* *somewhere* *sometime*
[CODE]String sqlQueryToGetHighestKeyValue = "SELECT MAX(cid) FROM customer;"[/CODE]
Are you sure MAX(cid) is a function that can be used? Because when I use that I ger error message, but if do "SELECT cid FROM customer" I get can get the set of all cid from which i could write an little algorithm to get the highest cid. I would like to know if MAX(cid) works?
Cheers guys, thanx for the help.
You have to have a GROUP BY clause. Adding GROUP BY cid should work. - MOD
"SELECT MAX(cid) FROM customer;" should work without a group by, even in access, i would have thought.
Be warned though, that this can be very slow on some systems. For example on postgres, it's recommended that you do like this:
select cid from customer order by cid DESC LIMIT 1;
since max won't use the index on cid in postgres. If access doesn't support the "limit 1" part you could leave itout, but you may be back at square one.
Either way, if this is going to be a multi user database you are heading for trouble. If it's only single user, then you know that no-one else will attempt this operation at the same time so it will work, but all it will take is 2 processes to read the last cid and try to insert a new one and you'll get a unique index constraint violation for the second process.
Why not just do all this in a single, serializable transaction? You won't have all these problems getting the key then. - MOD
another argument against natural keys is that the fields are defined by business rules/reqs that you may not have control over. if someday a field goes away or changes in such a way that it no longer uniquely identifies the tuples, you are in trouble. since you *do* have full control over a surrogate key, changes in the business rules/reqs won't have nearly the same impact.
I agree with this.
You decouple the primary key from the business rules when you use surrogate keys. You can alter constraints and tables to your heart's content, but the primary key and all associated foreign keys can be undisturbed.
I still think it's a better design, even if you're not doing data warehousing. - MOD
> Why not just do all this in a single, serializable
> transaction? You won't have all these problems
> getting the key then. - MOD
You can do this by keeping a special table with a record containing the next number. A transaction must do "select for update" to get the next number at the start of the transaction. This will guarantee that other users running similar transactions will wait for your transaction to finish before getting to read the next serial number.
It's workable, if all transactions follow the rules, but it's hell on concurrency and throughput. It's also unneccessary in modern database systems with their advanced isolation levels, updateable cursors and sequences.
The only problem with sequences is that you can lose numbers if transactions rollback. It's the price you pay for high concurrency and throughput. If lost numbers are not acceptable (such as for a cash register journal) then the serial number allocation must be part of the transaction and concurrent tranasctions must wait for other transactions to commit or rollback, as in the approach given above.
I'm writing a little bit of code now for hsqldb, which doesn't support sensitive/updateable cursors so I'm just gonna re-issue the select after a successful batch of updates. None too fancy, but it works (with minor caveats, like if data you inserted doesn't match the original search criteria then they'll just disappear from the list after saving them) :-)
Has anyone suggested this SQL hack:
SELECT @@IDENTITY
For many DBMS's this returns a one-row/one-col result set whose content is the last value of
last autoincrement variable you bumped up. In other words, just what you need. It should work
even in the presence of concurrent connections from other users, but check your documentation.
Does this work with Access, which was what the OP was stuck with? I seem to remember that Access97
didn''t support this but Access2000 did. So if you're not to worried about portability, launch Access and
type a bit of SQL and see if it recognizes this.
