Help with IF NOT EXiSTS

[nbiggs]

I get the following error if I try to use the IF NOT EXISTS function.
If I take out the "IF NOT EXISTS", the statement creates the index.
The same happens when I try using IF NOT EXISTS in a CREATE table
statement.

CREATE UNIQUE INDEX IF NOT EXISTS idx_table ON table (field1, field2);
SQL error: near "NOT": syntax error

Any ideas? Thanks for your help.

Nathan Biggs

[Igor Tandetnik]

Post by nbiggs
I get the following error if I try to use the IF NOT EXISTS function.
If I take out the "IF NOT EXISTS", the statement creates the index.
The same happens when I try using IF NOT EXISTS in a CREATE table
statement.
CREATE UNIQUE INDEX IF NOT EXISTS idx_table ON table (field1, field2);
SQL error: near "NOT": syntax error

I've never seen such syntax. SQLite does not support it. What is it
supposed to mean anyway?

If you want to create an index unless one exists already, just try to
create it and check for failure.

Igor Tandetnik

[Paul Tomblin]

Post by Igor Tandetnik

Post by nbiggs
CREATE UNIQUE INDEX IF NOT EXISTS idx_table ON table (field1, field2);
SQL error: near "NOT": syntax error

I've never seen such syntax. SQLite does not support it. What is it
supposed to mean anyway?

I think it's a MySQL-ism.

[nbiggs]

It is supposed to create the index if it does not exist already without
any errors.

I found the on the sqlite web site at
www.sqlite.org/lang_createindex.html, so I expected it to work since the
header on the web page is SQL As Understood By SQLite.




-----Original Message-----
From: Igor Tandetnik [mailto:itandetnik-***@public.gmane.org]
Sent: Monday, January 30, 2006 10:45 AM
To: SQLite
Subject: [sqlite] Re: Help with IF NOT EXiSTS

Post by nbiggs
I get the following error if I try to use the IF NOT EXISTS function.
If I take out the "IF NOT EXISTS", the statement creates the index.
The same happens when I try using IF NOT EXISTS in a CREATE table
statement.
CREATE UNIQUE INDEX IF NOT EXISTS idx_table ON table (field1, field2);
SQL error: near "NOT": syntax error

I've never seen such syntax. SQLite does not support it. What is it
supposed to mean anyway?

If you want to create an index unless one exists already, just try to
create it and check for failure.

Igor Tandetnik

[d***@public.gmane.org]

Post by nbiggs
I get the following error if I try to use the IF NOT EXISTS function.
If I take out the "IF NOT EXISTS", the statement creates the index.
The same happens when I try using IF NOT EXISTS in a CREATE table
statement.
CREATE UNIQUE INDEX IF NOT EXISTS idx_table ON table (field1, field2);
SQL error: near "NOT": syntax error
Any ideas? Thanks for your help.

The IF NOT EXISTS syntax was added to version 3.3.0 on
2006-01-04. If you upgrade from 3.2.x (or whatever it
is you are using) it should work.

--
D. Richard Hipp <***@hwaci.com>