Re: [sqlite] primary key quinky dink

On 19 Aug 2019, at 7:20pm, dboland9 wrote: > That is what I am using. I'm sorry. I missed that. Yes, you have the right solution now. I don't know where you got "AUTO_INCREMENT" from but it's not part of SQLite. ___ sqlite-users mailing list

Re: [sqlite] primary key quinky dink

That is what I am using. Thanks for the pointer to the FAQ. Dave, Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Monday, August 19, 2019 2:15 PM, Simon Slavin wrote: > On 19 Aug 2019, at 6:56pm, dboland9 dbola...@protonmail.com wrote: > > > I have created a number of

Re: [sqlite] primary key quinky dink

On 19 Aug 2019, at 6:56pm, dboland9 wrote: > I have created a number of tables, and two of then are part of a many to many > relationship. Thus, I need to get the last row id. Using the browser, What browser ? No part of the SQLite distribution is called a browser. > I find that: >

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

On Mon, Nov 26, 2018 at 1:19 PM Richard Hipp wrote: > On 11/23/18, Digital Dog wrote: > > I think these changes would not add a lot of code to sqlite and will make > > life easier for users. > > SQLite remembers the database schema by storing the original CREATE > TABLE text in the

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

On 11/23/18, Digital Dog wrote: > I think these changes would not add a lot of code to sqlite and will make > life easier for users. SQLite remembers the database schema by storing the original CREATE TABLE text in the sqlite_master table. That means the CREATE TABLE is reparsed whenever a new

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

> 25 nov. 2018, 20:14 Digital Dog wrote: > > I may have phrased the subject in a wrong way which misguided you. Sorry, I was misguided by my limited experience with PRIMARY KEY's. I use to combine these with WITHOUT ROWID. As you referred to and quoted from the documentation, it is only in

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

> From: E.Pasma > Digital Dog wrote: > > > > PRAGMA default_without_rowid = on > > To make all tables created while the directive is in use the WITHOUT ROWID > > tables. > .. > > > > PRAGMA enforce_not_null_on_primary_key = on > > For WITHOUT ROWID tables it would be a no-op, but for rowid

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

Digital Dog wrote: > > PRAGMA default_without_rowid = on > To make all tables created while the directive is in use the WITHOUT ROWID > tables. .. > > PRAGMA enforce_not_null_on_primary_key = on > For WITHOUT ROWID tables it would be a no-op, but for rowid tables it would > restore correct

Re: [sqlite] primary key in another column

You are right, Igor. Clear case of XY problem. I will remove trigger. Roman Sent from my T-Mobile 4G LTE Device Original message From: Igor Tandetnik <i...@tandetnik.org> Date: 1/26/18 9:03 PM (GMT-05:00) To: sqlite-users@mailinglists.sqlite.org Subject: Re: [

Re: [sqlite] primary key in another column

On 1/26/2018 6:20 PM, Roman Fleysher wrote: I think I effectively did as you suggested using triggers. I insert NULL into the ID column to create a row. This triggers the trigger to run update on the table to populate the columns based on the just created ID. Is this what you suggested?

Re: [sqlite] primary key in another column

of Richard Damon [rich...@damon-family.org] Sent: Friday, January 26, 2018 6:50 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column Couldn't you have it access a view which adds the columns by calculation rather than the raw table? (and if you have some tables

Re: [sqlite] primary key in another column

] Sent: Friday, January 26, 2018 6:26 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column One question I have, couldn't you just omit the fileName column from the able, and compute it in the select query that is getting the data? On 1/26/18 6:03 PM

Re: [sqlite] primary key in another column

...@mailinglists.sqlite.org] on behalf of Richard Damon [rich...@damon-family.org] Sent: Friday, January 26, 2018 6:26 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column One question I have, couldn't you just omit the fileName column from the able, and compute

Re: [sqlite] primary key in another column

One question I have, couldn't you just omit the fileName column from the able, and compute it in the select query that is getting the data? On 1/26/18 6:03 PM, Roman Fleysher wrote: My implementation of "for Each row" requires all columns to be populated. It is a dumb thing: forEachRow

Re: [sqlite] primary key in another column

From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Igor Tandetnik [i...@tandetnik.org] Sent: Friday, January 26, 2018 6:10 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column On 1/26/2018 6:03 PM, Roman Fleysher wrote: >

Re: [sqlite] primary key in another column

On 1/26/2018 6:03 PM, Roman Fleysher wrote: My implementation of "for Each row" requires all columns to be populated. It is a dumb thing: You said: After table is filled, an operation "for each row" will... I suggest running this UPDATE statement at the end of "table is filled", before "an

Re: [sqlite] primary key in another column

f Igor Tandetnik [i...@tandetnik.org] Sent: Friday, January 26, 2018 5:56 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column On 1/26/2018 5:47 PM, Roman Fleysher wrote: > I will use this table as a manager. There will be multiple columns holding > various

Re: [sqlite] primary key in another column

On 1/26/2018 5:47 PM, Roman Fleysher wrote: I will use this table as a manager. There will be multiple columns holding various file names. The names can be random, but I want humans to be able to easily inspect. After table is filled, an operation "for each row" will get files in some columns

Re: [sqlite] primary key in another column

qlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Igor Tandetnik [i...@tandetnik.org] Sent: Friday, January 26, 2018 5:33 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column On 1/26/2018 4:43 PM, Roman Fleysher wrote: > I would like to use prim

Re: [sqlite] primary key in another column

On 1/26/2018 4:43 PM, Roman Fleysher wrote: I would like to use primary key as a way to create unique column entry: CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL) such that file name is always prefix followed by the ID for the content to be: ID fileName 1 prefix_1 2

Re: [sqlite] primary key in another column

Solved with trigger, but I can not use NOT NULL for the fileName column: CREATE TRIGGER AAA AFTER INSERT ON A BEGIN UPDATE A SET fileName = 'prefix'||NEW.id WHERE id=NEW.id; END; INSERT INTO A (id) VALUES (NULL); INSERT INTO A (id) VALUES (NULL); INSERT INTO A (id) VALUES (NULL); ... Is that

Re: [sqlite] Primary Key without DataAnnotation for Windows Runtime

Benedikt Neuhold wrote: > > we are building an App for Windows 8 and want to use sqlite as local DB. We > want to separate our Models from our DB Engine. So we need a way to set the > Primary Key without DataAnnotation. Is there a way? > I'm trying to figure out if I'm understanding your

Re: [sqlite] Primary Key without DataAnnotation for Windows Runtime

We achieved something similar by doing the following: Creating a seperate assembly for our entities Define the primary key attribute in that assembly, but inside the SQLite namespace. Comment out the primary key attribute in the SQLite/net project to avoid re-defining it. We were then able to

Re: [sqlite] Primary Key without DataAnnotation for Windows Runtime

If I understood you right... 1) Create teable without primary key. 2) Separately create unique index on the column you want to be primary key. > Hi, > > > > we are building an App for Windows 8 and want to use sqlite as local DB. We > want to separate our Models from our DB Engine. So we need

Re: [sqlite] Primary key used in compound index

Hi guys, Just a little update for somebody who could have the same problem. I built the test application with SQLite version 3.8.4.2 (before it was built with 3.6.17) I got the following results with the original db (with the incorrect primary key - `id` INTEGER NOT NULL PRIMARY KEY

Re: [sqlite] Primary key used in compound index

On 2014/03/27 12:12, Clemens Ladisch wrote: RSmith wrote: Double quotes are not part of the standard, just allowed by most systems in lieu of single quotes No. To quote : | There are four ways of quoting keywords in SQLite: | | 'keyword' A keyword

Re: [sqlite] Primary key used in compound index

26/03/2014 12:50 (GMT+00:00) To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Primary key used in compound index On 25 Mar 2014, at 8:19pm, Pavel Vazharov <pa...@anchorworks.com> wrote: > "id" INTEGER NOT NULL PRIMARY

Re: [sqlite] Primary key used in compound index

RSmith wrote: > On 2014/03/27 02:50, Scott Robison wrote: >> Double quotes are part of the SQL-92 standard. I'm pretty sure they are, >> anyway. They are used (along with other database specific means) to delimit >> identifiers that would otherwise be illegal. > > Double quotes are not part of the

Re: [sqlite] Primary key used in compound index

On 2014/03/27 03:37, Pavel Vazharov wrote: Hi guys, I tried the thing that you proposed. The EXPLAIN QUERY PLAN showed selectid order fromdetail 0 0 0 SEARCH TABLE event AS e USING INDEX IDX_event_1 (deleted=? AND major=? AND rowid>?) as you expected. But the

Re: [sqlite] Primary key used in compound index

Hi guys, I tried the thing that you proposed. The EXPLAIN QUERY PLAN showed selectid order fromdetail 0 0 0 SEARCH TABLE event AS e USING INDEX IDX_event_1 (deleted=? AND major=? AND rowid>?) as you expected. But the timing is still as in the old verions (two

Re: [sqlite] Primary key used in compound index

On Wed, Mar 26, 2014 at 7:12 PM, RSmith wrote: > > Double quotes are not part of the standard, just allowed by most systems > in lieu of single quotes, but you are correct about back-quotes not being > part of the standard either, but they will be understood by most SQL >

Re: [sqlite] Primary key used in compound index

On 2014/03/27 02:50, Scott Robison wrote: Double quotes are part of the SQL-92 standard. I'm pretty sure they are, anyway. They are used (along with other database specific means) to delimit identifiers that would otherwise be illegal. As for using backtick to delimit identifiers, it is not

Re: [sqlite] Primary key used in compound index

On Wed, Mar 26, 2014 at 6:12 PM, RSmith wrote: > > Also, why all the Double-quotes around your column names in the create > statement? I'm sure it will work but it isn't the right SQL compatible > method, either use SQL backquotes or no quotes. Single and double quotes >

Re: [sqlite] Primary key used in compound index

On 2014/03/27 02:30, Richard Hipp wrote: On Wed, Mar 26, 2014 at 8:12 PM, RSmith > wrote: In your case it does so using SQLite version 3.8.2 while you have the other app you are making write to the same DB using SQLite 3.6.17... I'm

Re: [sqlite] Primary key used in compound index

On Wed, Mar 26, 2014 at 8:12 PM, RSmith wrote: > In your case it does so using SQLite version 3.8.2 while you have the > other app you are making write to the same DB using SQLite 3.6.17... I'm > surprised you have not corrupted the DB yet, > It is possible to create a

Re: [sqlite] Primary key used in compound index

Hi Pavel, You wrote: *** A) What version of SQLite are you using ? - We are using SQLite version 3.6.17 - We are using SQLite through the C++ SOCI library - 3.1.0 and then: Also,

Re: [sqlite] Primary key used in compound index

Hi guys, I tried the proposed thing to change the index to this: CREATE INDEX IDX_event_1 ON event (deleted, major); Now EXPLAIN QUERY PLAN returns: explain query plan select e.type, e.id, e.rsub_id, e.person_id, e.timestamp, e.file_id, e.previous_name, e.previous_folder_id, e.transparent,

Re: [sqlite] Primary key used in compound index

SEARCH TABLE event AS e USING INTEGER PRIMARY KEY (rowid>?) *** Let me know if you need some more information that I can provide. I'm about to test the proposed - "id" IN

Re: [sqlite] Primary key used in compound index

Simon Slavin wrote: > On 26 Mar 2014, at 1:20pm, Clemens Ladisch wrote: >> In this case, it appears that listing the rowid explicitly confuses >> SQLite somehow. > > That would be a bug, then ? > > Well, not a bug leading to incorrect results, but certainly one that slows >

Re: [sqlite] Primary key used in compound index

26/03/2014 12:50 (GMT+00:00) To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Primary key used in compound index On 25 Mar 2014, at 8:19pm, Pavel Vazharov <pa...@anchorworks.com> wrote: > "id" INTEGER NOT NULL PRIMARY

Re: [sqlite] Primary key used in compound index

On 26 Mar 2014, at 1:20pm, Clemens Ladisch wrote: > In this case, it appears that listing the rowid explicitly confuses > SQLite somehow. That would be a bug, then ? Well, not a bug leading to incorrect results, but certainly one that slows down searches. Simon.

Re: [sqlite] Primary key used in compound index

On Tue, 25 Mar 2014 20:19:40 + Pavel Vazharov wrote: > Hi guys, > > > I've the following scenario (I'm presenting a simplified version of > our scenario, but the main problem remains the same as in this > scenario): > > > CREATE TABLE test_event > ( > "id" INTEGER

Re: [sqlite] Primary key used in compound index

Pavel Vazharov wrote: > CREATE TABLE test_event > ( > "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > ... > ); > CREATE INDEX IDX_test_event_1 ON test_event (deleted, major, id); > > explain query plan select * from test_event where deleted = 0 and major = 1 > and id > 5 order by id asc limit

Re: [sqlite] Primary key used in compound index

On 25 Mar 2014, at 8:19pm, Pavel Vazharov wrote: > "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Can you try this as exactly the following: "id" INTEGER PRIMARY KEY, leaving everything else out. I assure you that it will obey the 'NOT NULL' and 'AUTOINCREMENT'

Re: [sqlite] primary key with bulk insert (UNION SELECT)

-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies Sent: Friday, October 12, 2012 8:42 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] primary key with bulk insert (UNION SELECT) On 11 October 2012 15:07, Alan Frankel <alan.fran...@mathworks.

Re: [sqlite] primary key with bulk insert (UNION SELECT)

On 11 October 2012 15:07, Alan Frankel wrote: > I have a table that uses an autogenerated id as primary key. I want to do > bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I > specify an id for each row: > > sqlite> create table

Re: [sqlite] primary key with bulk insert (UNION SELECT)

On 11 Oct 2012, at 3:07pm, Alan Frankel wrote: > I have a table that uses an autogenerated id as primary key. I want to do > bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I > specify an id for each row: > > sqlite> create table

Re: [sqlite] Primary Key uniqueness

On 03/29/2012 06:27 PM, Joe Bennett wrote: > Hi, > > I have three sqlite3 datbases that currently have no primary key (actually > done with two and stuck on the third). I am converting them to 'new' tables > with a primary key like so: > > create table if not exists new_table >

Re: [sqlite] Primary Key uniqueness

Try omitting 'rowid' - you are not grouping by it and if you did it is unique so no dups. select Column_1,Column_27,Column_47 , count(*) from old_table group by Column_1,Column_27,Column_47 having count(*) > 1 JG On 29 March 2012 17:27, Joe Bennett

Re: [sqlite] Primary Key uniqueness

On 29 Mar 2012, at 5:27pm, Joe Bennett wrote: > No rows meet this criteria... So, I'm looking for a better way to find the > non unique data in the old_table so I can clear this error and insert it > into the new table... Any ideas? Instead of using the plain INSERT ...

Re: [sqlite] primary key autoincrement updation problemincaserefereced

Fixed it. It allows duplicates when stmt reset(sqlite3_reset) missed after sqlite3_step(..) in transaction. Thank you. On Thu, Jan 12, 2012 at 4:27 PM, Simon Slavin wrote: > > On 12 Jan 2012, at 10:52am, Durga D wrote: > > > I have attached sample db. I tried to

Re: [sqlite] primary key autoincrement updation problemincaserefereced

On 12 Jan 2012, at 10:52am, Durga D wrote: > I have attached sample db. I tried to simulate with test application. > But, I could not. > >Please find the attached db, which has only one table. You can't attach things to messages to this list, because we don't want the list flooded with

Re: [sqlite] primary key autoincrement updation problemincaserefereced

On 12 Jan 2012, at 10:14am, Durga D wrote: > create table if not exists t0 ( > h integer primary key autoincrement, > t0info text, unique(t0info)); > > t0 table allows duplicate t0info column, when in transaction. > > how to avoid duplicates for t0info column. If your table was

Re: [sqlite] primary key autoincrement updation problemincaserefereced

Thank you. create table if not exists t0 ( h integer primary key autoincrement, t0info text, unique(t0info)); t0 table allows duplicate t0info column, when in transaction. how to avoid duplicates for t0info column. Thanks in advance. Durga. On Thu, Jan 12, 2012 at 3:37 PM,

Re: [sqlite] primary key autoincrement updation problemincaserefereced

On 12 Jan 2012, at 6:05am, Durga D wrote: >Insertion time, I am able to get the rowid from > sqlite3_last_insert_rowid(); > > It's working fine. > > Now, t0info record already exists, that time , I should not update > with latest rowid. I should fetch the corresponding h

Re: [sqlite] primary key autoincrement updation problemincaserefereced

Corrected it. Thank you. insert into t1( hh, t1info) values ((select h from t0 where t0info = ?), ?) On Thu, Jan 12, 2012 at 11:35 AM, Durga D wrote: > Dear Igor, > > Insertion time, I am able to get the rowid from > sqlite3_last_insert_rowid(); > >It's

Re: [sqlite] primary key autoincrement updation problemincaserefereced

Dear Igor, Insertion time, I am able to get the rowid from sqlite3_last_insert_rowid(); It's working fine. Now, t0info record already exists, that time , I should not update with latest rowid. I should fetch the corresponding h value, update in t1 table. Here, fetching is

Re: [sqlite] primary key autoincrement updation problemincaserefereced

Durga D wrote: > Dear Igor, > >If already toinfo record exists, that time how to get the h > (primary key) (instead of rowid)? An INTEGER PRIMARY KEY column is in fact an alias for rowid. They are one and the same. -- Igor Tandetnik

Re: [sqlite] primary key autoincrement updation problem incaserefereced

Dear Igor, If already toinfo record exists, that time how to get the h (primary key) (instead of rowid)? Any shortcut for this? Thanks in advance, Durga. On Wed, Jan 11, 2012 at 7:19 PM, Durga D wrote: > Thanks alot !!! > > Have a good day. Let me try. >

Re: [sqlite] primary key autoincrement updation problem incaserefereced

Thanks alot !!! Have a good day. Let me try. On Wed, Jan 11, 2012 at 7:15 PM, Igor Tandetnik wrote: > Durga D wrote: > > so, first I need to insert in t0 table, then query for value from t0, > then > > update t1 with this value. Needs to avoid

Re: [sqlite] primary key autoincrement updation problem incaserefereced

Durga D wrote: > so, first I need to insert in t0 table, then query for value from t0, then > update t1 with this value. Needs to avoid reading from t0 table. You don't need to query t0 after insert. You can use sqlite3_last_insert_rowid API function, or

Re: [sqlite] primary key autoincrement updation problem in caserefereced

Thanks Igor. >>You seem to be under impression that a FOREIGN KEY >>constraint somehow magically pulls a value from >>referenced table. correct. so, first I need to insert in t0 table, then query for value from t0, then update t1 with this value. Needs to avoid reading from t0 table. Is

Re: [sqlite] primary key autoincrement updation problem in caserefereced

Durga D wrote: > create table if not exists t0 ( h integer primary key autoincrement, t0info > text); > create table if not exists t1 ( i integer primary key autoincrement, t1info > text, hh integer not null, unique(i, hh), foreign key (hh) references t0 > (h) ); The UNIQUE

Re: [sqlite] primary key autoincrement updation problem in case refereced

Simon/Jr Please find the attached main.c files. Thanks, Durga On Wed, Jan 11, 2012 at 12:58 PM, Simon Slavin wrote: > > On 11 Jan 2012, at 6:05am, Durga D wrote: > > > But, sqlite3_step(stmtoft1); is failing always. What might be the wrong > > with this approach. > >

Re: [sqlite] primary key autoincrement updation problem in case refereced

On 11 Jan 2012, at 6:05am, Durga D wrote: > But, sqlite3_step(stmtoft1); is failing always. What might be the wrong > with this approach. What code is it reporting when it fails ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] primary key autoincrement updation problem in case refereced

On 11/01/12 06:58, Durga D wrote: hh field updation in t1 table is the issue. If I do in sqlite shell, it's working fine. In C language only, I have this issue. > Any idea? > hmm, you'd need to provide (excerpts of) the actual code before I could say any more. if you can post (as plain

Re: [sqlite] primary key autoincrement updation problem in case refereced

then, how to insert the record with binding and step apis in C language with second approach? hh field updation in t1 table is the issue. If I do in sqlite shell, it's working fine. In C language only, I have this issue. Any idea? On Wed, Jan 11, 2012 at 12:17 PM, jr

Re: [sqlite] primary key autoincrement updation problem in case refereced

On 11/01/12 06:29, Durga D wrote: Deleted sqlite data base before executing first approach. and again delete sqlite database before executing second approach. ok, misread this and thought 'first approach' relevant. Issue is related to database design or insertion procedure. from what I

Re: [sqlite] primary key autoincrement updation problem in case refereced

Deleted sqlite data base before executing first approach. and again delete sqlite database before executing second approach. Issue is related to database design or insertion procedure. There is no syntax problems in queries also. On Wed, Jan 11, 2012 at 11:54 AM, jr

Re: [sqlite] primary key autoincrement updation problem in case refereced

On 11/01/12 06:05, Durga D wrote: if I understand correctly: first you created tables t1 and t2, then you say 'create if not exist' -- but they already do. instead of (re-)creating tables t1 and t2, ALTER TABLE statements can be used to modify the existing. jr.

Re: [sqlite] primary key and rowid

Simon Slavin writes: > > On 19 Dec 2011, at 7:04am, YJM YAN wrote: > > //Second way create primary key: > > CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x AUTOINCREMENT)); > > "x" being an alias for the rowid? > > Second way will work too. It correctly declares "x INTEGER" and SQLite >

Re: [sqlite] primary key and rowid

On 19 Dec 2011, at 7:04am, YJM YAN wrote: > //First way create primary key: > CREATE TABLE t(x INTEGER PRIMARY KEY AUTOINCREMENT, y, z); > "x" being an alias for the rowid? First way will work. > //Second way create primary key: > CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x AUTOINCREMENT));

Re: [sqlite] Primary Key size

On Wed, 17 Aug 2011 01:39:57 -0700 (PDT), LiranR wrote: > >Hi all ! >A quick question: >Do you know if there is a different in performance when i use 4 bytes >Integer Primary Key or 8 bytes Integer Primary Key? The INTEGER type in SQLite is a 64bit signed integer. It is

Re: [sqlite] Primary Key

Database' Subject: Re: [sqlite] Primary Key How about this: 'CREATE TABLE TwoPrimaryFields (primaryField1 TEXT NOT NULL, primaryField2 TEXT NOT NULL, someOtherField TEXT, PRIMARY KEY (primaryField1, primaryField2)) From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun

Re: [sqlite] Primary Key

How about this: 'CREATE TABLE TwoPrimaryFields (primaryField1 TEXT NOT NULL, primaryField2 TEXT NOT NULL, someOtherField TEXT, PRIMARY KEY (primaryField1, primaryField2)) From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andi Suhandi Sent: 30 March

Re: [sqlite] primary key on two columns of an associative table

On 29/03/2011, at 12:59 AM, Sam Carleton wrote: > The system calls for an associative table, a table with two foriegn > keys to two other tables, allowing for a many to many relationship. > Is there any way to make the primary key be both the columns? Yes, you can define a primary key on two

Re: [sqlite] primary key on two columns of an associative table

On Mon, Mar 28, 2011 at 09:59:59AM -0400, Sam Carleton scratched on the wall: > The system calls for an associative table, a table with two foriegn > keys to two other tables, allowing for a many to many relationship. > Is there any way to make the primary key be both the columns? > > CREATE

Re: [sqlite] PRIMARY KEY - not unique?

On Wed, Jun 30, 2010 at 5:37 PM, Steve Hurst wrote: > Hi all, not sure if this is a bug or user error - any insight appreciated! > > I have a schema that defines a two column primary key on a table - the docs > indicate this will create a unique composite key.

Re: [sqlite] PRIMARY KEY - not unique?

ah, sorry ... Am 01.07.2010 09:49, schrieb Jan: > Which row (node_id, attribute) is not unique? > > Jan > > Am 30.06.2010 23:37, schrieb Steve Hurst: >> Hi all, not sure if this is a bug or user error - any insight appreciated! >> >> I have a schema that defines a two column primary key on a

Re: [sqlite] PRIMARY KEY - not unique?

Which row (node_id, attribute) is not unique? Jan Am 30.06.2010 23:37, schrieb Steve Hurst: > Hi all, not sure if this is a bug or user error - any insight appreciated! > > I have a schema that defines a two column primary key on a table - the docs > indicate this will create a unique composite

Re: [sqlite] primary key is indexed by default ?

Richard Kim <[EMAIL PROTECTED]> wrote: > Just wondering that if you set primary key in table, the column is > indexed by default in sqlite ? Yes. PRIMARY KEY and UNIQUE constraints are enforced by automatically generating a unique index. Igor Tandetnik

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

On Mon, 18 Feb 2008 12:32:55 +, [EMAIL PROTECTED] wrote: >The AUTOINCREMENT keyword prevents an primary key from being >reused even after it is deleted. Thanks for the clarification. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

On Mon, 18 Feb 2008 23:31:32 +1100, BareFeet <[EMAIL PROTECTED]> wrote: >I can create a view to display the date in my localtime: Thanks much for the example. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

Gilles Ganault <[EMAIL PROTECTED]> wrote: > On Sat, 16 Feb 2008 11:29:29 +1100, BareFeet > <[EMAIL PROTECTED]> wrote: > > If you designate an integer column as also being the primary key, > > then SQLite will auto assign its value incrementally each time > > you insert a new row, unless you

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

Hi Gilles, >> If you designate an integer column as also being the primary key, >> then SQLite will auto assign its value incrementally each time you >> insert a new row, unless you assign a value explicitly. > > In this case, why do we need to use "PRIMARY KEY AUTOINCREMENT"? Adding

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

On Sat, 16 Feb 2008 11:29:29 +1100, BareFeet <[EMAIL PROTECTED]> wrote: > If you designate an integer column as also being the primary key, > then SQLite will auto assign its value incrementally each time > you insert a new row, unless you assign a value explicitly. In this case, why do we need

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

Hi Gilles, > 1. what happens if I leave out "PRIMARY KEY" and just use INTEGER? Um, then it won't be a primary key. Integer just means a whole number. Primary key means that the database will treat that column as the unique identifier for each row. In actuality this means that SQLite will

[sqlite] RE : [sqlite] primary key constraint

What is the difference between : 1/ userid UNSIGNED INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL 2/ userid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL CHECK (userid>0) --- David Nguyen <[EMAIL PROTECTED]> a écrit : > Hello, > > With php, I want to create 2 tables, userid is a > primary key

Re: [sqlite] primary key information

another way "SELECT rowid FROM MyTable LIMIT 1" the column name returned will be the Integer Primary Key StanD sd at sqlight.com - Original Message - From: "Mario Frasca" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Monday, August 07, 2

Re: [sqlite] primary key information

Nemanja Corlija wrote: I think in any other case unique index would be created as expected. sqlite> drop table test; sqlite> create table test (ni integer, pk_name varchar(32) primary key, info integer); sqlite> pragma index_list(test); 0|sqlite_autoindex_test_1|1 sqlite> pragma

Re: [sqlite] primary key information

On 8/7/06, Mario Frasca <[EMAIL PROTECTED]> wrote: I was a bit wondering: does the declaration 'primary key' actually produce an indexing? or does that happen only if I explicitly ask for a (unique) index? It usually does create unique index for you. But in this case, INTEGER PRIMARY KEY, it

Re: [sqlite] primary key information

Nemanja Corlija wrote: If you turn the headers on (.header ON) you'll see that the last column of table_info() output is "pk". This column indicates weather or not the table column in question is part of primary key. Though that last column of table_info() pragma is missing from documentation

Re: [sqlite] primary key information

On 8/7/06, Mario Frasca <[EMAIL PROTECTED]> wrote: hallo, list... how do I get primary key information about a table? [EMAIL PROTECTED]:~$ sqlite3 /data/mariof/test.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> drop table test; sqlite> create table test (pk integer primary

Re: [sqlite] primary key and physical data organization

Martin Pfeifle wrote: Hi, Assume I have a table containing an integer primary key. As far as I know, this value is identical to the ROWID, right? How does SQLite organize the data within the file? Does it try to organize the data on the pages according to the primary key (=ROWID) or according

Re: [sqlite] primary key and physical data organization

Martin Pfeifle <[EMAIL PROTECTED]> wrote: > Hi, > Assume I have a table containing an integer primary key. As far as I know, > this value is identical to the ROWID, right? Correct > How does SQLite organize the data within the file? > Does it try to organize the data on the pages according to

Re: [sqlite] Primary Key

Robert Simpson wrote: -Original Message- From: andy [mailto:[EMAIL PROTECTED] Sent: Sunday, February 12, 2006 6:38 PM To: sqlite-users@sqlite.org Subject: [sqlite] Primary Key I'm using sqlite 3.2.8 I have a table: create table lookups ( id integer, descr varchar(80),

RE: [sqlite] Primary Key

> -Original Message- > From: andy [mailto:[EMAIL PROTECTED] > Sent: Sunday, February 12, 2006 6:38 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Primary Key > > I'm using sqlite 3.2.8 > > I have a table: > > create table lookups > ( >id integer, >descr varchar(80), >

Re: [sqlite] Primary Key doenst work

Thanks a lot! It really helped! Christian Kienle http://www.QtForum.org On Nov 21, 2004, at 6:52 PM, [EMAIL PROTECTED] wrote: Christian Kienle <[EMAIL PROTECTED]> writes: I am using SQLite

Re: [sqlite] Primary Key doenst work

Christian Kienle <[EMAIL PROTECTED]> writes: > I am using SQLite 2.8.5 Firstly, 2.8.5 is ancient. Numerous bugs have been fixed between 2.8.5 and the current version in the 2.8 branch: 2.8.15. You really should upgrade. > and I wanna use a primary key field in one of my tables. > > ~$ sqlite

Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index

<[EMAIL PROTECTED]> wrote : > Greg Obleshchuk wrote: >> >> So in these cases there is no benefit from creating an index on a column >> that is INTEGER PRIMARY KEY? >> > > Putting an index on an INTEGER PRIMARY KEY will make INSERT, > DELETE, and UPDATE slower since the index must be

  1   2   >