Defining Primary Keys For Real Studio Apps

An issue that seems to come up fairly regularly with people new to database applications in Real Studio is how to make unique record identifiers when they insert data.  The simple answer is that you don’t – the database itself already knows how to do this.

SQLite databases (REALSQLDatabase in Real Studio) automatically have a rowid regardless if you define your own primary key or not.  You can access this rowid via sql and, in fact, have to use if you want to edit a recordset (if you don’t have a primary key defined) or you’ll get a database error along the lines of it not knowing which record to edit.  However, as soon as you define the primary key field it, and the rowid, are one in the same and hold the same value.

I’ve seen some developers come up with elaborate schemes to come up with unique primary id’s for the database tables.  This is a bad idea!  That database can (and should) do that for you.  It’s exceptionally dangerous to come up with your own scheme.  Bugs happen, mistakes happen, and the problem only gets worse when you add in multiple user databases where multiple connections can insert data at any time and when you have billions, or trillions, of rows of data.  Let the database do that work for you!  It was designed to do that afterall.

I think part of the problem is the built-in Database Editor in Real Studio is awful.  It doesn’t give you the option of making an auto-increment field.  See screenshot below:

 

 

 

We ditched the built-in editor many years ago for other tools.  For SQLite we use a variety of tools and they ALL do a better job of managing your SQLite database than Real Studio.  For example, take a look at the options that Base http://menial.co.uk/base/ gives us just for the primary key:

 

The same goes for all the other databases that Real Studio supports.  There are tools that are genuinely better in almost every aspect that the Real Studio Database Editor.  Do a little research and you’ll find tons of tools that range from free to hundreds of dollars.  If you want editors that are similar across all databases I’d recommend Navicat http://www.navicat.com.

A lot of our projects use SQLite.  We use straight SQL to create our tables, indexes, and relationships.  Here is an example of defining an integer primary key that auto increments:

Create Table IF NOT EXISTS  T_Account(Account_ID    INTEGER  PRIMARY KEY AUTOINCREMENT, ShortAcctNumber    TEXT NULL …..

We always use the tools to create the database during development and then have the tools generate the initial SQL for us.  It’s simple and fast and I don’t have to remember the details.  I just define the fields and then copy and paste the SQL into the project.  I’m lazy that way.

If you’re looking for example of this process you can take a look at our subscription training videos at http://www.bkeeney.com/RealStudioTraining/realstudiotraining.cgi.  Our Journal Entry project goes from start to finish creating the database from scratch via code and the showing how to add, edit, and delete data from the database.

Depending upon the rowid can be detrimental to the future of your project.  They can be reused whereas a primary key cannot.  Take a look at this blog post from Marco over at SQLABS:  http://www.sqlabs.com/blog/2010/12/sqlite-and-unique-rowid-something-you-really-need-to-know/.

A quick scan of MySQL and PostgreSQL documentation shows that they don’t use rowid so if you have to migrate from SQLite to a database server your rowid code is now invalid and you’ll rewrite your code to make and use a primary key.  So it’s just easier to start defining your primary, integer, auto increment keys now and start using the primary key field instead of rowid.

Let the primary key work for you.  Don’t create your own scheme for numbering your primary key field and for your own sanity ditch the Real Studio Database Editor.  Happy coding!

7 thoughts on “Defining Primary Keys For Real Studio Apps

  1. In that screen shot from Base I really like that balloon style dialog with the primary key options… I wonder if that can be done in RS… Probably would take declares (sigh)…

    I wonder if it can be faked using a container control (thus making it Xplatform) … probably not given all the problems with overlapping controls in RB… Though overlaying a textedit control myself over multiple cells in my mergable listbox class seems to work…

    Anyway I agree the built in DB editor in RB is horrific… It has never been usable- though I admit I have not tried it recently. You would think RS could do a LOT better.

    There seems to be a # of free ones out there that are better than the RS supplied one so it seems strange that REAL’s is not better.

    What I start with is the FireFox add-in SQLite Manager, and manually tweak the SQL it produces to handle what’s not built it… but my needs have been pretty simple.

  2. Good article, Bob.

    The Base 2 application looks good. I was not aware of that tool until your article. I’ve been using SQLiteManager from SQLabs which as worked well for me.

    bb

  3. @Karen
    It’s funny, I probably have 3 or 4 different SQLite editors. They each do something just a little different that makes it more, or less, useful depending upon the situation.

    Base doesn’t handle encrypted databases. SQLiteManager does but it’s interface for some reason irritates me. Navicat has a very good visual SQL editor but it’s a little clunky in other area. The FireFox add-in is an interesting choice and is usually what I do when I need something in Windows.

  4. @BobB
    Thanks!

    I believe there are several reasons why RS hasn’t updated the Database Editor. The first is that there are many editors available that are inexpensive. Two, they don’t use it for anything in the IDE so much like reporting it’s not an irritation to them. Finally, at Real World 2012 Norman hinted at future database improvements so it may just be a matter of getting the new IDE out and adding the new database classes.

    Regardless, it causes no end to problems for new developers when they go to use it and they struggle with something as basic as an auto incrementing primary key. It shouldn’t ever be a problem!

  5. The PostgreSQL version for an integer primary key is “BIGSERIAL PRIMARY KEY”.

    There are cases where the database is holding details of objects which have a non-integer identified, such as UUID or URI, which could be used as primary key; however in these cases I would still tend to use the incrementing integer as primary key, and the object identifier as as secondary indexed key. That way if an object is being transferred from one database to another, the secondary key can still be used but the more efficient primary key can be used in ordinary operations.
    On a practical point, if this object has a property “DatabaseID” which holds the primary key from the database and which has default value -1 when created, it is very easy to test in code whether the object has been saved into the database.

  6. A good article. I remember when the REALbasic mySQL plugin did not return the key of the last record created if you asked for it, so you had to create a method of creating your own primary key when creating records across different tables that use the same key. I still maintain systems where this is done, and it’s too much hassle to change. Of course auto increment integers are best, as long as you can find out what the key is after you create a record! The systems where my technique is used have relatively low record volumes, so it’s really not worth changing.

  7. @Eric
    Well, the plugin didn’t automatically return the last insert ID but using a simple query *did* return it (I think it was ‘select @@’ or something like that. PostgreSQL uses ‘SELECT LASTVAL();’.

    We used this method in MySQL for years (and actually still might use the query in several apps) before GetInsertID made its way into the plugin.

Comments are closed.