Deleting an entry and releasing from unique key index

Question

For a project with offline storage, I created a database with a primary key for the id and also a unique field holding the date.

When I delete an entry, I can not create a new one with the same date the deleted entry had.

What can I do to get the date out of the index when I delete that entry?

This is how I create Table:

CREATE TABLE IF NOT EXISTS userdata (id INTEGER PRIMARY KEY ASC, entrydate DATE UNIQUE, strength, comments

I wonder if I need to add something to tell the DB server to allow me to use the same value again as soon it is free again. Maybe I need to run some kind of an update, where SQLite updates its internal records.

Dora

Answer 1

I think there are a few possibilities here.

1. Your delete statement is in an uncommitted transaction. So the unique value hasn't actually been removed from the table before your attempt to insert.

2. The value you are deleting and the new value you are inserting are not actually the same value. Run a select and make sure the value you are attempting to insert is actually available.

3. You have a corrupt index and need to reindex it.

Replica

Yes I use transactions. That might be the reason. Do you know how can I commit a transaction? – Dora

Take a look here: grox.net/doc/sqlite/lang_transaction.html I also updated the answer with that linq. Pay close attention to requirements for committing succesfully. – Replica

I tried this, but it seems not to work in the HTML5 environment. i used the transactions how described in STEP 5 on http://www.html5rocks.com/en/tutorials/webdatabase/todo/ – Dora

I figured it out, googled and added: db.transaction(function(tx){tx.executeSql("COMMIT",[]);}); --- @RThomas: thank you very much. I would not have figured it out without your help. – Dora