Page 1 of 1

Getting the last record

Posted: Fri Dec 07, 2007 5:31 pm
by tanvirtonu
I need to generate my primary key from my application bcos my PK has a specific format(e.g. Cl2006-09). Now to do this I need to know the last record/primary key of the last row(or only one record if exists only one)so dat I can generate my new PK by adding up to this last value. By which query I can know only the last PK of my table (or only one PK if only one exists.)

Posted: Fri Dec 07, 2007 7:11 pm
by Christopher
You should use a sequence (or auto_increment value) because you have a possible race condition.

Posted: Fri Dec 07, 2007 7:16 pm
by califdon
I agree with arborint. You may need a formatted "key" field for the outside world, but you will save yourself a lot of potential issues if you always stick with an auto-increment field for the real primary key. You can always format the "external" key using the actual primary key value, so your users will never know the difference, but you will have a rock solid control of your relational database!

Posted: Sat Dec 08, 2007 3:08 am
by tanvirtonu
arborint wrote:You should use a sequence (or auto_increment value) because you have a possible race condition.
What is a " possible race condition"

If I agree with [s]u[/s] you to keep an auto-increment PK field then what if-
I want to insert data in two tables from a single from. And these two tables has a parent-child relationship. Now if I insert data by the following query, will there be any problem? I mean- I want to insert [s]tha[/s] the same PK for the child table as its FK. And to do this, I will make both PK and Fk auto-increment integer field and first insert data in parent table and then child leaving the Pk and Fk field null.

Code: Select all

Insert into parentTable values(null,'John','Doctor');
Insert into childTable values(null,'ABC Hospital','Address');
in Db table can I have these values-
parentTable-
1 John Doctor
childTable-
1 ABC Hospital Address

and will there be any problem after delete .[/quote]
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:11. Please use proper, complete spelling when posting in the forums. AOL Speak, leet speak and other abbreviated wording can confuse those that are trying to help you (or those that you are trying to help). Please keep in mind that there are many people from many countries that use our forums to read, post and learn. They do not always speak English as well as some of us, nor do they know these aberrant abbreviations. Therefore, use as few abbreviations as possible, especially when using such simple words.

Some examples of what not to do are ne1, any1 (anyone); u (you); ur (your or you're); 2 (to too); prolly (probably); afaik (as far as I know); etc.

Posted: Sat Dec 08, 2007 2:03 pm
by califdon
I am not following your logic. First of all, never make a foreign key an auto-increment, it must always contain the value of the primary key of another table.

The purpose of a primary key is to insure that every record in a table has a unique identifier. A foreign key is a field in one table that contains the value of a primary key in a different table.

You must begin with what entities you are representing and what are their relationships. From your brief explanation, it would seem that your entities are doctors and hospitals and that there is a one-to-many relationship, or possibly a many-to-many relationship between them. Which is correct? In either case, each table must have a primary key. If a doctor can be associated with more than one hospital (many-to-many), you will need a third table, containing just the primary key values that establish the relationships (but in this table, they will be foreign keys). If a doctor is always associated with just one hospital (one-to-many), it is simpler, because you only need to have a foreign key in the doctor's table, which is always the value of the primary key in the hospital table.

To answer your question, a race condition is when the possibility exists that you cannot predict with certainty which of two operations will finish first. In this case, it refers to the possibility that if two users are entering records, the values of the keys could potentially become exchanged, depending on whether one transaction is delayed. By using an auto-increment field, the database software will manage this, instead of the application code that you write.