0

:I have this table:

id            |      name          |      chapter      |    book

 1                  hamlet                  I               Hamlet

 2                  Ismael                  IV              Moby Dick

The behaviour I expect for new values is:

Same name && same book => Character exists so Same id than the existing one:

 2                   Ismael                   X              Moby Dick

Same name && different book => Character doesn't exist so Changes the id: 

 3                   Ismael                  XX              The Bible

My question is:

Do I have to make the query before insert new values and then insert the new value?

or there is a way to do it in a automatic way by setting up a trigger or something?

My CREATE TABLE statement

    db.execSQL("CREATE TABLE characters (id INTEGER NOT NULL , 
   name TEXT NOT NULL , chapter TEXT NOT NULL , book TEXT NOT NULL );");
butelo
  • 1,653
  • 1
  • 18
  • 29

1 Answers1

0

I think the most elegant way of doing so would be for you to isolate the properties of your rows which permit to identify them UNIQUELY. Lets say for instance that name + book make the line unique, then you could concatenate these strings into one identifier in your object, add a column to your database, put and index on it and search on this index when you want to insert new elements.

Sephy
  • 50,022
  • 30
  • 123
  • 131
  • Yeah I'll do it that way. I've just assumed that like we have a constraint for Unique values we could also have ways to manage repeated ones. So there's no way to do it the way I ask? just for documentation pourposes. – butelo Nov 08 '11 at 12:24
  • unicity is ensured by primary keys if I'm not mistaken so you could also declare the pair (name, book) as primary key. I might suffice. Something like that : http://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns – Sephy Nov 08 '11 at 12:31
  • well actually it would have to be three values: (name, book, chapter) because same character appears in multiple chapters at the same book – butelo Nov 08 '11 at 12:47