On Fri, 9 Jan 2009 10:05:49 -0800, ed <epdm...@gmail.com> wrote in General Discussion of SQLite Database <sqlite-users@sqlite.org>:
>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
