[sqlite] Making data unique

[Bart Smissaert]

Ah, yes they both work and tell me the indexed fields.
Saves me some work parsing this out from the table create SQL.
Thanks for that.

RBS


On Thu, Dec 10, 2015 at 10:19 AM, Simon Slavin  wrote:

>
> On 10 Dec 2015, at 10:17am, Bart Smissaert 
> wrote:
>
> > This will show in sqlite_master like this:
> >
> > type   name   tbl_name rootpage sql
> >
> ---
> > index sqlite_autoindex_dataset_1 dataset  717
> >
> > So, no SQL. Problem with this is that there is no way to see what fields
> > the index is on.
>
> What happens if you do either of
>
> PRAGMA index_info(sqlite_autoindex_dataset_1);
> PRAGMA index_xinfo(sqlite_autoindex_dataset_1);
>
> ?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Making data unique

[Simon Slavin]

On 10 Dec 2015, at 10:17am, Bart Smissaert  wrote:

> This will show in sqlite_master like this:
> 
> type   name   tbl_name rootpage sql
> ---
> index sqlite_autoindex_dataset_1 dataset  717
> 
> So, no SQL. Problem with this is that there is no way to see what fields
> the index is on.

What happens if you do either of

PRAGMA index_info(sqlite_autoindex_dataset_1);
PRAGMA index_xinfo(sqlite_autoindex_dataset_1);

?

[sqlite] Making data unique

[Bart Smissaert]

This will show in sqlite_master like this:

type   name   tbl_name rootpage sql
---
index sqlite_autoindex_dataset_1 dataset  717

So, no SQL. Problem with this is that there is no way to see what fields
the index is on.
I suppose the only way round this is to look at the table create SQL.
Is this just the way it is?

RBS

On Tue, Dec 8, 2015 at 2:52 AM, Keith Medcalf  wrote:

>
> create table dataset
> (
>id integer not null,
>timestamp integer not null,
>data integer not null,
>unique (id, timestamp)
> );
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Andrew Stewart
> > Sent: Monday, 7 December, 2015 08:01
> > To: 'SQLite mailing list'
> > Subject: [sqlite] Making data unique
> >
> > Hi,
> > I have a table that consists of 3 elements:
> > ID - integer
> > Date/time - integer
> > Data - integer
> > A single ID can exist multiple times.
> > A single Date/time can exist multiple times.
> > An ID & Date/time combination is unique.
> >
> > What is the best way to ensure uniqueness in this table.
> >
> > Thanks,
> > Andrew Stewart
> > Software Designer
> >
> > Argus Controls
> > #101 - 18445 53 AVE
> > Surrey, BC  V3S 7A4
> >
> > t: 1-888-667-2091  ext : 108
> > t: 1-604-536-9100  ext : 108
> > f: 604-538-4728
> > w: www.arguscontrols.com<http://www.arguscontrols.com/>
> > e: astewart at arguscontrols.com<mailto:astewart at arguscontrols.com>
> >
> > Notice: This electronic transmission contains confidential information,
> > intended only for the person(s) named above. If you are not the intended
> > recipient, you are hereby notified that any disclosure, copying,
> > distribution, or any other use of this email is strictly prohibited. If
> > you have received this transmission by error, please notify us
> immediately
> > by return email and destroy the original transmission immediately and all
> > copies thereof.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Making data unique

[Andrew Stewart]

Thanks Keith.
This works.

Andrew

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
Sent: Monday, December 07, 2015 6:53 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Making data unique


create table dataset
(
   id integer not null,
   timestamp integer not null,
   data integer not null,
   unique (id, timestamp)
);

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of 
> Andrew Stewart
> Sent: Monday, 7 December, 2015 08:01
> To: 'SQLite mailing list'
> Subject: [sqlite] Making data unique
> 
> Hi,
> I have a table that consists of 3 elements:
> ID - integer
> Date/time - integer
> Data - integer
> A single ID can exist multiple times.
> A single Date/time can exist multiple times.
> An ID & Date/time combination is unique.
> 
> What is the best way to ensure uniqueness in this table.
> 
> Thanks,
> Andrew Stewart
> Software Designer
> 
> Argus Controls
> #101 - 18445 53 AVE
> Surrey, BC  V3S 7A4
> 
> t: 1-888-667-2091  ext : 108
> t: 1-604-536-9100  ext : 108
> f: 604-538-4728
> w: www.arguscontrols.com<http://www.arguscontrols.com/>
> e: astewart at arguscontrols.com<mailto:astewart at arguscontrols.com>
> 
> Notice: This electronic transmission contains confidential 
> information, intended only for the person(s) named above. If you are 
> not the intended recipient, you are hereby notified that any 
> disclosure, copying, distribution, or any other use of this email is 
> strictly prohibited. If you have received this transmission by error, 
> please notify us immediately by return email and destroy the original 
> transmission immediately and all copies thereof.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Making data unique

[Keith Medcalf]

create table dataset
(
   id integer not null,
   timestamp integer not null,
   data integer not null,
   unique (id, timestamp)
);

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Andrew Stewart
> Sent: Monday, 7 December, 2015 08:01
> To: 'SQLite mailing list'
> Subject: [sqlite] Making data unique
> 
> Hi,
> I have a table that consists of 3 elements:
> ID - integer
> Date/time - integer
> Data - integer
> A single ID can exist multiple times.
> A single Date/time can exist multiple times.
> An ID & Date/time combination is unique.
> 
> What is the best way to ensure uniqueness in this table.
> 
> Thanks,
> Andrew Stewart
> Software Designer
> 
> Argus Controls
> #101 - 18445 53 AVE
> Surrey, BC  V3S 7A4
> 
> t: 1-888-667-2091  ext : 108
> t: 1-604-536-9100  ext : 108
> f: 604-538-4728
> w: www.arguscontrols.com<http://www.arguscontrols.com/>
> e: astewart at arguscontrols.com<mailto:astewart at arguscontrols.com>
> 
> Notice: This electronic transmission contains confidential information,
> intended only for the person(s) named above. If you are not the intended
> recipient, you are hereby notified that any disclosure, copying,
> distribution, or any other use of this email is strictly prohibited. If
> you have received this transmission by error, please notify us immediately
> by return email and destroy the original transmission immediately and all
> copies thereof.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Making data unique

[Andrew Stewart]

I don't want to overwrite.  An error or rejecting the Insert will work.

Thanks,
Andrew

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Adam Devita
Sent: Monday, December 07, 2015 7:14 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Making data unique

When you are about to insert into the table and find that ID & Date/time are 
identical to another record, but  the data is different, do you want to 
overwrite, or not?  Do you want an error?

Adam

On Mon, Dec 7, 2015 at 10:01 AM, Andrew Stewart  
wrote:
> Hi,
> I have a table that consists of 3 elements:
> ID - integer
> Date/time - integer
> Data - integer
> A single ID can exist multiple times.
> A single Date/time can exist multiple times.
> An ID & Date/time combination is unique.
>
> What is the best way to ensure uniqueness in this table.
>
> Thanks,
> Andrew Stewart
> Software Designer
>
> Argus Controls
> #101 - 18445 53 AVE
> Surrey, BC  V3S 7A4
>
> t: 1-888-667-2091  ext : 108
> t: 1-604-536-9100  ext : 108
> f: 604-538-4728
> w: www.arguscontrols.com<http://www.arguscontrols.com/>
> e: astewart at arguscontrols.com<mailto:astewart at arguscontrols.com>
>
> Notice: This electronic transmission contains confidential information, 
> intended only for the person(s) named above. If you are not the intended 
> recipient, you are hereby notified that any disclosure, copying, 
> distribution, or any other use of this email is strictly prohibited. If you 
> have received this transmission by error, please notify us immediately by 
> return email and destroy the original transmission immediately and all copies 
> thereof.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Making data unique

[Andrew Stewart]

Hi,
I have a table that consists of 3 elements:
ID - integer
Date/time - integer
Data - integer
A single ID can exist multiple times.
A single Date/time can exist multiple times.
An ID & Date/time combination is unique.

What is the best way to ensure uniqueness in this table.

Thanks,
Andrew Stewart
Software Designer

Argus Controls
#101 - 18445 53 AVE
Surrey, BC  V3S 7A4

t: 1-888-667-2091  ext : 108
t: 1-604-536-9100  ext : 108
f: 604-538-4728
w: www.arguscontrols.com
e: astewart at arguscontrols.com

Notice: This electronic transmission contains confidential information, 
intended only for the person(s) named above. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or any other use of this email is strictly prohibited. If you have received 
this transmission by error, please notify us immediately by return email and 
destroy the original transmission immediately and all copies thereof.

[sqlite] Making data unique

[Adam Devita]

When you are about to insert into the table and find that ID &
Date/time are identical to another record, but  the data is different,
do you want to overwrite, or not?  Do you want an error?

Adam

On Mon, Dec 7, 2015 at 10:01 AM, Andrew Stewart
 wrote:
> Hi,
> I have a table that consists of 3 elements:
> ID - integer
> Date/time - integer
> Data - integer
> A single ID can exist multiple times.
> A single Date/time can exist multiple times.
> An ID & Date/time combination is unique.
>
> What is the best way to ensure uniqueness in this table.
>
> Thanks,
> Andrew Stewart
> Software Designer
>
> Argus Controls
> #101 - 18445 53 AVE
> Surrey, BC  V3S 7A4
>
> t: 1-888-667-2091  ext : 108
> t: 1-604-536-9100  ext : 108
> f: 604-538-4728
> w: www.arguscontrols.com
> e: astewart at arguscontrols.com
>
> Notice: This electronic transmission contains confidential information, 
> intended only for the person(s) named above. If you are not the intended 
> recipient, you are hereby notified that any disclosure, copying, 
> distribution, or any other use of this email is strictly prohibited. If you 
> have received this transmission by error, please notify us immediately by 
> return email and destroy the original transmission immediately and all copies 
> thereof.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1