Re: [sqlite] KEY keyword
ed wrote: > however, I am still not clear if the conflict resolution clause will > be used > on a table with no UNIQUE or PRIMARY KEY columns? It may if there are other constraints, such as CHECK or NOT NULL. For more details, see http://sqlite.org/lang_conflict.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] KEY keyword
Igor,thank you for the explanation however, I am still not clear if the conflict resolution clause will be used on a table with no UNIQUE or PRIMARY KEY columns? I would assume not but i couldn't find the answer in the documentation. thanks, ed On Fri, Jan 9, 2009 at 11:17 AM, Igor Tandetnik wrote: > ed wrote: > > I have a sqlite 3.3.4 app using a db with the following schema: > > > > CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR); > > > > Is the KEY keyword utilized? > > The way this statement is parsed, column 'n' has the type of 'INTEGER > KEY'. Remember, SQLite allows almost any odd sequence of identifiers as > a type name (except that it can't contain certain keywords that > introduce column-constraint clause, e.g. PRIMARY). You could just as > well write > > CREATE TABLE my_data(n I LOVE SQLITE, ...); > > (try it - it does work). > > > Will it act the same as a primary key ? > > No. > > > Ultimately, i'm trying to determine if the KEY will enforce a unique > > constraint > > No. But PRIMARY KEY or UNIQUE will. > > > such that the following insert's conflict clause would > > even be necessary. > > Conflict resolution clause is never necessary. If none is specified, the > default of ABORT is used. > -- > With best wishes, >Igor Tandetnik > > With sufficient thrust, pigs fly just fine. However, this is not > necessarily a good idea. It is hard to be sure where they are going to > land, and it could be dangerous sitting under them as they fly > overhead. -- RFC 1925 > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] KEY keyword
On Fri, 9 Jan 2009 10:05:49 -0800, ed wrote in General Discussion of SQLite Database : >Hello, >I have a sqlite 3.3.4 app using a db with the following schema: > >CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR); > >Is the KEY keyword utilized? According to the syntax diagrams http://www.sqlite.org/lang_createtable.html it isn't. >Will it act the same as a primary key ? If you need column n to be a primary key, define my_data as: CREATE TABLE my_data( n INTEGER PRIMARY KEY, s INTEGER, p INTEGER, od VARCHAR ); -- this is the preferred, and mosst efficient form. -- http://www.sqlite.org/lang_createtable.html -- tells why or CREATE TABLE my_data( n INTEGER UNIQUE, s INTEGER, p INTEGER, od VARCHAR ); -- this will work, but it will be a little bit slower. >Ultimately, i'm trying to determine if the KEY will enforce >a unique constraint, such that the following insert's >conflict clause would even be necessary. > >INSERT OR REPLACE INTO elevator_data >(id, schedule, panel, output_data) VALUES That depends on what you need. Assuming the "n INTEGER PRIMARY KEY" definition above, INSERT INTO elevator_data (id, schedule, panel, output_data) VALUES ..; will throw an error if you try to insert a duplicate key. INSERT OR REPLACE INTO elevator_data (id, schedule, panel, output_data) VALUES ..; will delete the original row and insert the new one. None of the original columns values in the affected row will survive. >thanks, >ed -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] KEY keyword
ed wrote: > I have a sqlite 3.3.4 app using a db with the following schema: > > CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR); > > Is the KEY keyword utilized? The way this statement is parsed, column 'n' has the type of 'INTEGER KEY'. Remember, SQLite allows almost any odd sequence of identifiers as a type name (except that it can't contain certain keywords that introduce column-constraint clause, e.g. PRIMARY). You could just as well write CREATE TABLE my_data(n I LOVE SQLITE, ...); (try it - it does work). > Will it act the same as a primary key ? No. > Ultimately, i'm trying to determine if the KEY will enforce a unique > constraint No. But PRIMARY KEY or UNIQUE will. > such that the following insert's conflict clause would > even be necessary. Conflict resolution clause is never necessary. If none is specified, the default of ABORT is used. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] KEY keyword
Hello, I have a sqlite 3.3.4 app using a db with the following schema: CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR); Is the KEY keyword utilized? Will it act the same as a primary key ? Ultimately, i'm trying to determine if the KEY will enforce a unique constraint, such that the following insert's conflict clause would even be necessary. INSERT OR REPLACE INTO elevator_data (id, schedule, panel, output_data) VALUES thanks, ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
