sqlite & flex - insert into if not exists?

问题:

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!

回答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.

回答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();