sqlite & flex - insert into if not exists?

Question

I'm using flex to develop my first desktop app and I'm working with sqlite for the first time as well.

I'm creating my database and all the tables and I would also like to add a few rows of data into a couple of the tables so that the users have some data to work with on first install.

The only problem I'm having is every time I run the program it keeps inserting the same data over and over again.

I'm just wondering if its possible to do a - INSERT INTO IF NOT EXISTS. or some other kind of work around.

thanks!

Answer 1

Insert duplicate data with the same primary key and use a "IGNORE" conflict clause:

sqlite> create table t(i integer primary key not null);
sqlite> insert into t values(1);
sqlite> insert or ignore into t values(1);
sqlite> select * from t;
1

Duplicate values will not be inserted, and the statement will complete successfully.

Alternatively, you can use "UNIQUE" constraint instead of a primary key:

sqlite> create table t(i integer unique not null);
sqlite> insert into t values(1);
sqlite> insert or ignore into t values(1);
sqlite> select * from t;
1

The idea is that some constraint will get violated and the row will be ignored.

Answer 2

Thanks for the insight but I'm still not having any luck.

Here's my code

stmt.text = "CREATE TABLE IF NOT EXISTS tbl_breed ("+" breed_id INTEGER PRIMARY KEY AUTOINCREMENT, "+"  breed_breed TEXT)";
stmt.execute();
stmt.text = "INSERT OR IGNORE INTO tbl_breed (breed_breed)"+" VALUES ('Test')";
stmt.execute();

Ok so I fixed the problem - I guess you have to hard code primary key here's what i did

stmt.text = "CREATE TABLE IF NOT EXISTS tbl_breed ("+" breed_id INTEGER PRIMARY KEY AUTOINCREMENT, "+"  breed_breed TEXT)";
stmt.execute();
stmt.text = "INSERT OR IGNORE INTO tbl_breed (breed_id,breed_breed)"+" VALUES ('1','test')";
stmt.execute();