Re: [sqlite] TEXT PRIMARY KEY
Am 12.03.2012 12:40, schrieb Kit: 2012/3/12, Christoph P.U. Kukulies: INSERT OR IGNORE INTO resource ... INSERT OR REPLACE INTO instance ... Thanks a lot. What is the idea behind the INSERT OR REPLACE in your solution? Christoph If you edit a test for some version of your software, md5sum is changed too. You must replace row in `instance`. Thanks. I have the structure now in a shape I think it would be useful: CREATE TABLE instance ( mandant TEXT , md5sum TEXT, basename TEXT, prob_ref TEXT, suite TEXT , tag TEXT , size INT, creation_date TEXT, last_write_time TEXT, PRIMARY KEY (mandant,basename,prob_ref,suite,tag) ); CREATE TABLE resource ( md5sum TEXT PRIMARY KEY, data BLOB); The FS tree contained about a million files and a corresponding tar-archive comprises about 70 GB. The database with gzipped BLOBS now is 1.7GB in size. Each files' location is made of \mandant\prob_ref\tag\suite\basename. My only question now is: when I collect the files and INSERT or IGNORE the resource, I have to read in the file and build the BLOB, independently on whether the INSERT really has to take place. A quick beforehand decision whether an INSERT has to be made, could possibly speed up the file collection process. I was using a try clause on the INSERT and if that wouldn't fail I was doing an UPDATE on just the BLOB. Other ideas? -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/12 Christoph P.U. Kukulies : > Wouldn't the second and the third run result in being the records just > replaced (since they have the > same data in all columns and I don't have any uniqueness defined). > > But instead I have three identical entries from each run. > Christoph I recommend to add an attribute `version`. Version of main application, not test. Add primary key or unique. CREATE TABLE instance ( path TEXT, basename TEXT, version TEXT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, FOREIGN KEY (md5sum) REFERENCES resource (md5sum), PRIMARY KEY (path,basename,version) ); You may try s/PRIMARY KEY/UNIQUE/ -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Here's again the schema: CREATE TABLE instance ( path TEXT, basename TEXT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, FOREIGN KEY (md5sum) REFERENCES resource (md5sum) ); CREATE TABLE resource ( md5sum TEXT, data BLOB, primary key(md5sum) ); INSERT OR IGNORE INTO resource ... INSERT OR REPLACE INTO instance ... Thanks a lot. What is the idea behind the INSERT OR REPLACE in your solution? Christoph If you edit a test for some version of your software, md5sum is changed too. You must replace row in `instance`. It just happened that I ran over the directory tree a second and a third time. Wouldn't the second and the third run result in being the records just replaced (since they have the same data in all columns and I don't have any uniqueness defined). But instead I have three identical entries from each run. -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/12, Christoph P.U. Kukulies : >> INSERT OR IGNORE INTO resource ... >> INSERT OR REPLACE INTO instance ... > > Thanks a lot. What is the idea behind the INSERT OR REPLACE in your > solution? > Christoph If you edit a test for some version of your software, md5sum is changed too. You must replace row in `instance`. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Am 10.03.2012 09:06, schrieb Kit: 2012/3/9 Christoph P.U. Kukulies: CREATE TABLE instance ( path TEXT, basename TEXT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, FOREIGN KEY (md5sum) REFERENCES resource (md5sum) ); CREATE TABLE resource ( md5sum TEXT, data BLOB, primary key(md5sum) ); What makes the contents of two files equal (so that their contents can be represented by the same resource) ? md5sum = md5sum My problem: what do I have to change in TABLE instance so that I can use it to determine whether the key is already in the resource TABLE? - Make new md5sum from new data INSERT OR IGNORE INTO resource ... INSERT OR REPLACE INTO instance ... Thanks a lot. What is the idea behind the INSERT OR REPLACE in your solution? -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/9 Christoph P.U. Kukulies : CREATE TABLE instance ( path TEXT, basename TEXT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, FOREIGN KEY (md5sum) REFERENCES resource (md5sum) ); CREATE TABLE resource ( md5sum TEXT, data BLOB, primary key(md5sum) ); > What makes the contents of two files equal (so that their contents can be > represented by the same resource) ? md5sum = md5sum > My problem: what do I have to change in TABLE instance so that I can use it > to determine whether the key is already > in the resource TABLE? - Make new md5sum from new data INSERT OR IGNORE INTO resource ... INSERT OR REPLACE INTO instance ... > Would that be a FOREIGN KEY? And how would I do that in syntax? > If I need FOREIGN KEY, would I have to enable that in SQLite somehow (at > compile time)? > Thanks, > Christoph PRAGMA foreign_keys = ON; in runtime. But you don't need foreign keys support in this case. You may use it for garbage collection. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Am 04.03.2012 15:22, schrieb Kit: 2012/3/4 Christoph P.U. Kukulies: CREATE TABLE dir(filename text, md5sum text, size int, content blob, PRIMARY KEY(filename,md5sum,size)); Coming back to your suggestion using PRIMARY KEY(filename,md5sum,size), how would I address this PRIMARY KEY, example: I'm building a table CREATE TABLE candidate (?,client TEXT, md5sum TEXT, basename TEXT, size INT, ...some other stuff ) The ? should stand for the - is that FOREIGN KEY? - key in the dir-TABLE which is formed of filename,md5sum,size. Christoph Foreign key is (basename,md5sum,size). Your example shows that a composite key in this case is possible, but it is not appropriate. Use simple key md5sum. Make two tables. Attribute "size" (part of primary key) is redundant. Kit, I'm coming back again on your suggestion. Maybe you can elaborate, why size is redundant and of what I should make two tables? Let me describe the problem again: I'm going recursively through a directory tree collection information on all files, that is, CREATE TABLE instance ( path TEXT, md5sum TEXT, basename TEXT, size INT, creation_date TEXT, last_write_time TEXT, ); CREATE TABLE resource ( size INT, name TEXT, md5sum TEXT, data BLOB, primary key(basename,md5sum,size) ); What makes the contents of two files equal (so that their contents can be represented by the same resource) ? md5sum = md5sum size = size name = name (allowing different names sharing the same resource is another thing - I might do that but do not want at the moment). So when I'm now visiting every file I will check whether the file with its contents has already been entered into the TABLE resource. My problem: what do I have to change in TABLE instance so that I can use it to determine whether the key is already in the resource TABLE? Would that be a FOREIGN KEY? And how would I do that in syntax? If I need FOREIGN KEY, would I have to enable that in SQLite somehow (at compile time)? Thanks, Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/4 Christoph P.U. Kukulies : >> CREATE TABLE dir(filename text, md5sum text, size int, content blob, >> PRIMARY KEY(filename,md5sum,size)); > > Coming back to your suggestion using PRIMARY KEY(filename,md5sum,size), how > would I address this PRIMARY KEY, example: > > I'm building a table > CREATE TABLE candidate (?,client TEXT, md5sum TEXT, basename TEXT, size INT, > ...some other stuff ) > The ? should stand for the - is that FOREIGN KEY? - key in the dir-TABLE > which is formed of filename,md5sum,size. > Christoph Foreign key is (basename,md5sum,size). Your example shows that a composite key in this case is possible, but it is not appropriate. Use simple key md5sum. Make two tables. Attribute "size" (part of primary key) is redundant. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Am 04.03.2012 10:31, schrieb Kit: 2012/3/4 Christoph P.U. Kukulies: Thanks for the ideas. The problem is with md5sum clashes: all files with 0 bytes have the same md5sum. Also files with same contents have the same md5sum but may have a different name. That's no problem. if you put names to another table, you may share one content. Normalize. One cell, one atomic information. You may use md5sum as "text primary key" or you may define PRIMARY KEY(filename,md5sum,size). Interesting. How would that work syntaxwise? I mean, at table creation time? CREATE TABLE dir(filename text, md5sum text, size int, content blob, PRIMARY KEY(filename,md5sum,size)); Coming back to your suggestion using PRIMARY KEY(filename,md5sum,size), how would I address this PRIMARY KEY, example: I'm building a table CREATE TABLE candidate (?,client TEXT, md5sum TEXT, basename TEXT, size INT, ...some other stuff ) The ? should stand for the - is that FOREIGN KEY? - key in the dir-TABLE which is formed of filename,md5sum,size. -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/4 Christoph P.U. Kukulies : > At the moment I have everything in the filesystem. The test situation is > that about 500 testprograms have to be run > with product release N against product release N-1. The test programs are > duplicated at the moment in all > places. The deployments of product release builds N, N-1, N-2 are also in > the filesystem. You may use Git with branches for different versions > If I boil it all down from, say 60 GB to 2 GB (as my first attempts resulted > in), I could generate the testbed on the push > of a button out of the database and assemble it in a tree with two branches, > the "new candidate" branch and the "reference" branch, > run the tests. That all could be web app based in the end. Git use own database solution optimized for performance and allows modify all glue scripts in userspace. You may personalize Git for different purposes. > Just a question on "NORMALIZATION": Is normalization being done on a > database by some operation, like > giving a command: "normalize it" or is it done by design of the data > structures being entered? > Christoph "Normalization" is manually process to minimize redundancy and dependency from data structures. See "Database normalization". You may normalize your table to 3 tables and denormalize to 2 tables. Replace attribute "size" with attribute "version". Primary key first table will be reduced to (filename,version), primary key second table to (md5sum). -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Am 04.03.2012 10:31, schrieb Kit: 2012/3/4 Christoph P.U. Kukulies: Thanks for the ideas. The problem is with md5sum clashes: all files with 0 bytes have the same md5sum. Also files with same contents have the same md5sum but may have a different name. That's no problem. if you put names to another table, you may share one content. Normalize. One cell, one atomic information. You may use md5sum as "text primary key" or you may define PRIMARY KEY(filename,md5sum,size). Interesting. How would that work syntaxwise? I mean, at table creation time? CREATE TABLE dir(filename text, md5sum text, size int, content blob, PRIMARY KEY(filename,md5sum,size)); Filename, version, date and size put to another table with md5sum as a foreign key. Inspire with Git system or use it. I will have a look at Git. Christoph If you planning version control system, you may select from completed systems, eg. Subversion, Git, Mercurial and Bazaar. Thanks for the above. Actually, I'm not planning a versioning system. I'm planning a database based testsuite. At the moment I have everything in the filesystem. The test situation is that about 500 testprograms have to be run with product release N against product release N-1. The test programs are duplicated at the moment in all places. The deployments of product release builds N, N-1, N-2 are also in the filesystem. If I boil it all down from, say 60 GB to 2 GB (as my first attempts resulted in), I could generate the testbed on the push of a button out of the database and assemble it in a tree with two branches, the "new candidate" branch and the "reference" branch, run the tests. That all could be web app based in the end. Just a question on "NORMALIZATION": Is normalization being done on a database by some operation, like giving a command: "normalize it" or is it done by design of the data structures being entered? -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/4 Christoph P.U. Kukulies : > Thanks for the ideas. The problem is with md5sum clashes: all files with 0 > bytes have the same md5sum. > Also files with same contents have the same md5sum but may have a different > name. That's no problem. if you put names to another table, you may share one content. >> Normalize. One cell, one atomic information. You may use md5sum as >> "text primary key" or you may define PRIMARY >> KEY(filename,md5sum,size). > > Interesting. How would that work syntaxwise? I mean, at table creation time? CREATE TABLE dir(filename text, md5sum text, size int, content blob, PRIMARY KEY(filename,md5sum,size)); >> Filename, version, date and size put to another table with md5sum as a >> foreign key. Inspire with Git system or use it. > > I will have a look at Git. > Christoph If you planning version control system, you may select from completed systems, eg. Subversion, Git, Mercurial and Bazaar. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Am 03.03.2012 19:29, schrieb Kit: 2012/3/3 Christoph P.U. Kukulies: I'm building a unique ID made up from the basename, md5sum and size of the file. This results as a TEXT PRIMARY KEY (e.g. filename_md5sum_size). Can I use (I'm using System.Data.SQLite) a try clause to find out whether the entry is already there? I think this can be faster than asking the database by an SQL statement, whether the KEY is already there. Christoph Thanks for the ideas. The problem is with md5sum clashes: all files with 0 bytes have the same md5sum. Also files with same contents have the same md5sum but may have a different name. Normalize. One cell, one atomic information. You may use md5sum as "text primary key" or you may define PRIMARY KEY(filename,md5sum,size). Interesting. How would that work syntaxwise? I mean, at table creation time? Filename, version, date and size put to another table with md5sum as a foreign key. Inspire with Git system or use it. I will have a look at Git. -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/3 Christoph P.U. Kukulies : > I'm building a unique ID made up from the basename, md5sum and > size of the file. This results > as a TEXT PRIMARY KEY (e.g. filename_md5sum_size). > Can I use (I'm using System.Data.SQLite) a try clause to find out whether > the entry is already there? > > I think this can be faster than asking the database by an SQL statement, > whether the KEY is already there. > Christoph Normalize. One cell, one atomic information. You may use md5sum as "text primary key" or you may define PRIMARY KEY(filename,md5sum,size). Filename, version, date and size put to another table with md5sum as a foreign key. Inspire with Git system or use it. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Am 02.03.2012 13:59, schrieb Jay A. Kreibich: On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies scratched on the wall: When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), would this imply uniqueness? Kind of. It implies uniqueness in the SQL sense, which does not include NULLs (remember, NULL != NULL). The SQL term "PRIMARY KEY" should imply both "UNIQUE" and "NOT NULL", but there is a long standing issue in SQLite that allows NULLs in non-integer PRIMARY KEY columns. This allows "duplicate" NULL entries in a PK column. Normally this isn't an issue, as you shouldn't have NULLs in a single-column PK anyways. Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? No, but to be extra safe you should write "TEXT PRIMARY KEY NOT NULL." -j Thanks. And thanks to others for contributing. My logic is the following: When I'm inserting files and their respective contents into the database, I have to decide, whether a file is already there. I'm building a unique ID made up from the basename, md5sum and size of the file. This results as a TEXT PRIMARY KEY (e.g. filename_md5sum_size). Can I use (I'm using System.Data.SQLite) a try clause to find out whether the entry is already there? I think this can be faster than asking the database by an SQL statement, whether the KEY is already there. -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
On Fri, Mar 02, 2012 at 02:21:19PM +0100, Benoit Mortgat scratched on the wall: > On Fri, Mar 2, 2012 at 13:59, Jay A. Kreibich wrote: > > On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies > > scratched on the wall: > > > > ??Kind of. ??It implies uniqueness in the SQL sense, which does not > > ??include NULLs (remember, NULL != NULL). > > Actually, NULL != NULL is unknown. As an expression, yes (if you consider NULL to be "unknown"). As a general statement, not really. Setting aside the theoretical argument of what, exactly, NULL means (e.g. "unknown" or something else), it is true that the SQL expression "NULL != NULL" will evaluate to "NULL", not "true." However, I was only trying to make a general statement that "one NULL is not equal to another," in the sense that "NULL == NULL" will not evaluate to "true"... which means it will not trip a UNIQUE constraint. This is why the PK constraint normally implies both UNIQUE and NOT NULL. Not only do NULLs not make sense in a PK as a fundamental identifier, allowing NULLs also breaks the concept that a PK should have a *known* unique (i.e. no NULLs allowed in comparisons) value for each row. SQLite does not imply NOT NULL when you specify a PK constraint. This is in contradiction to the SQL standard, but has been wrong so long nobody wants to risk changing it. Hence, if you want your SQLite PKs to have known unique values, you must specify not only PK, but explicitly specify NOT NULL. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
On Fri, Mar 2, 2012 at 13:59, Jay A. Kreibich wrote: > On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies > scratched on the wall: > > Kind of. It implies uniqueness in the SQL sense, which does not > include NULLs (remember, NULL != NULL). Actually, NULL != NULL is unknown. Any expression compared to NULL with any of the operators == != < > <= >= LIKE GLOB will have unknown result. C:\> sqlite3 SQLite version 3.7.10 2012-01-16 13:28:40 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT CASE WHEN NULL = NULL THEN 0 ...> WHEN NULL <> NULL THEN 1 ...> ELSE 2 ...>END; 2 sqlite> .q -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Hmmm...works for me... On Windows: SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table test(a text primary key); sqlite> insert into test values('1'); sqlite> insert into test values('1'); Error: column a is not unique On Linux: SQLite version 3.7.10 2012-01-16 13:28:40 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table test (a text primary key); sqlite> insert into test values('1'); sqlite> insert into test values('1'); Error: column a is not unique Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Oliver Peters [oliver@web.de] Sent: Friday, March 02, 2012 4:13 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] TEXT PRIMARY KEY Am 02.03.2012 11:03, schrieb Oliver Peters: sorry I meant CREATE TABLE test( a TEXT PRIMARY KEY ); (without INTEGER, usually I write INTEGER and not TEXT :-) ) > Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies: >> When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), > > yes > >> would this imply uniqueness? > > yes > >> Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? > > no and that doesn't make sense I'd say > >> > > [...] > > > simply try (untested) > > CREATE TABLE test( > a TEXT INTEGER PRIMARY KEY > ); > > INSERT INTO test(a) VALUES('1'); > INSERT INTO test(a) VALUES('1'); > > > > oliver > ___ > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies scratched on the wall: > When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), > would this imply uniqueness? Kind of. It implies uniqueness in the SQL sense, which does not include NULLs (remember, NULL != NULL). The SQL term "PRIMARY KEY" should imply both "UNIQUE" and "NOT NULL", but there is a long standing issue in SQLite that allows NULLs in non-integer PRIMARY KEY columns. This allows "duplicate" NULL entries in a PK column. Normally this isn't an issue, as you shouldn't have NULLs in a single-column PK anyways. > Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? No, but to be extra safe you should write "TEXT PRIMARY KEY NOT NULL." -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Am 02.03.2012 11:03, schrieb Oliver Peters: sorry I meant CREATE TABLE test( a TEXT PRIMARY KEY ); (without INTEGER, usually I write INTEGER and not TEXT :-) ) Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies: When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), yes would this imply uniqueness? yes Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? no and that doesn't make sense I'd say [...] simply try (untested) CREATE TABLE test( a TEXT INTEGER PRIMARY KEY ); INSERT INTO test(a) VALUES('1'); INSERT INTO test(a) VALUES('1'); oliver ___ 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] TEXT PRIMARY KEY
Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies: When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), yes would this imply uniqueness? yes Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? no and that doesn't make sense I'd say [...] simply try (untested) CREATE TABLE test( a TEXT INTEGER PRIMARY KEY ); INSERT INTO test(a) VALUES('1'); INSERT INTO test(a) VALUES('1'); oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/2, Christoph P.U. Kukulies : > When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), Yes. > would this imply uniqueness? Yes. > Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? > Christoph Kukulies No. PRIMARY KEY is always UNIQUE. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
On Fri, 02 Mar 2012 10:44:20 +0100, "Christoph P.U. Kukulies" wrote: > When defining a column TEXT PRIMARY KEY > (is that possible on TEXT?), Yes that is possible on any data type. > would this imply uniqueness? It would. > Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? No. Neither should you create a UNIQUE INDEX on the primary key column. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users