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!