Re: [sqlite] INSERT OR Replace Help
On Thu, Mar 25, 2010 at 4:44 PM, jose isaias cabrera wrote: > Create table test (t1 primary key, t2 secundary key, t3, t4); I am pretty certain that SQLite has no idea what 't1 primary key' means. Perhaps you meant to say 't1 integer primary key'? I am completely certain that SQLite has no idea what 't2 secundary key' means. First, perhaps you meant to write 'secondary' instead of 'secundary'. Even so, perhaps you meant to write 't2 integer secondary key'. Even so, there is no such syntax. Perhaps you meant to define a composite primary key on t1 and t2. In that case, use the syntax 'PRIMARY KEY (t1, t2)' -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR Replace Help
P Kishor wrote: > On Thu, Mar 25, 2010 at 4:44 PM, jose isaias cabrera > wrote: >> Create table test (t1 primary key, t2 secundary key, t3, t4); > > > I am pretty certain that SQLite has no idea what 't1 primary key' > means. No, it's perfectly OK. It means a column with no affinity, which also constitutes a primary key. > Perhaps you meant to say 't1 integer primary key'? Unlikely, seeing as the OP insersts strings into it. > I am completely certain that SQLite has no idea what 't2 secundary > key' means. Well, actually, it means a column named t2 whose declared type is "secundary key". Of course such a type has no special meaning to SQLite. One can as well write "create table test(t2 here be dragons)" (which is a valid SQLite statement). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR Replace Help
On Thu, Mar 25, 2010 at 5:06 PM, Igor Tandetnik wrote: > P Kishor wrote: >> On Thu, Mar 25, 2010 at 4:44 PM, jose isaias cabrera >> wrote: >>> Create table test (t1 primary key, t2 secundary key, t3, t4); >> >> >> I am pretty certain that SQLite has no idea what 't1 primary key' >> means. > > No, it's perfectly OK. It means a column with no affinity, which also > constitutes a primary key. > >> Perhaps you meant to say 't1 integer primary key'? > > Unlikely, seeing as the OP insersts strings into it. > >> I am completely certain that SQLite has no idea what 't2 secundary >> key' means. > > Well, actually, it means a column named t2 whose declared type is "secundary > key". Of course such a type has no special meaning to SQLite. One can as well > write "create table test(t2 here be dragons)" (which is a valid SQLite > statement). You are absolutely correct re. "secundary key" not having any special meaning to sqlite, which is precisely what I implied when I wrote "SQLite has no idea what 't2 secundary key' means. It is pointless syntax. With regards to having a PK on a column with no affinity, I guess things will just default to strings, no? In any case, for OP's purpose, and esp. since he seems to be inserting numbers as strings, as implied by the quoted numbers, he might benefit from CREATE TABLE test ( t1 TEXT, t2 TEXT, t3 TEXT, t4 TEXT, PRIMARY KEY(t1, t2) ); I don't know if INSERT or REPLACE will work in that case, but seems like it should. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR Replace Help
P Kishor wrote: > With regards to having a PK on a column with no affinity, I guess > things will just default to strings, no? No. Just as with any other column with no affinity, no conversions will take place. Thus, 1 and '1' will be considered distinct, and 20 will sort before '1'. > In any case, for OP's purpose, and esp. since he seems to be inserting > numbers as strings, as implied by the quoted numbers, he might benefit > from > > CREATE TABLE test ( > t1 TEXT, > t2 TEXT, > t3 TEXT, > t4 TEXT, > PRIMARY KEY(t1, t2) > ); > > I don't know if INSERT or REPLACE will work in that case, but seems > like it should. It will. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR Replace Help
"P Kishor" wrote... > In any case, for OP's purpose, and esp. since he seems to be inserting > numbers as strings, as implied by the quoted numbers, he might benefit > from > > CREATE TABLE test ( > t1 TEXT, > t2 TEXT, > t3 TEXT, > t4 TEXT, > PRIMARY KEY(t1, t2) > ); > > I don't know if INSERT or REPLACE will work in that case, but seems > like it should. Yes, the benefit is great. Thanks, that works. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
