Database: INSERTS, speed and primary keys

[David Bovill]

HI Monte - hows down-under :)

On 27 September 2010 11:31, Monte Goulding <monte at sweattechnologies.com>wrote:

>
> I haven't yet used SQLYoga but I'm sure Trevor has this all worked out.
> SQLite will handle the auto increment of integer primary keys when you
> INSERT without an ID field and then you query
> put revDataFromQuery(,,sDB,"select last_insert_rowid()") into tID
>

The issue is not the automatic creation on surrogate keys - and retrieving
of the value (as above). The issue is preventing adding of duplicate
records. I want to prevent adding the same hKey, say:

test_Command | c | stack "Test" | 1

to the database. Using INSERT with the primary key set to the autoincrement
id field, you would get records like this:

*id |      name       | type |   location   | hnum*
 1 | test_Command    |   c  | stack "Test" |  1
 2 | test_Command    |   c  | stack "Test" |  1
 3 | another_Command |   c  | stack "Test" |  1
 4 | another_Command |   c  | stack "Test" |  1

Only by making the primary key = "name,type,location,hnum" instead of "id"
woudl you get a fast normalisation of the data inserted.