Re: [sqlite] autoincrement and primary key

[Roman Fleysher]

Thank you, INTEGER -> INT solved the problem. According to manual, this will 
make search slower, but I give data integrity more weight for now.

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [i...@tandetnik.org]
Sent: Monday, May 20, 2013 4:41 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] autoincrement and primary key

On 5/20/2013 4:17 PM, Roman Fleysher wrote:
> I would like to use INTEGER PRIMARY KEY, but I would like to disable its 
> implicit AUTOINCREMENT feature. Namely, if INSERT specifies value of the 
> column, I would like uniqueness to be enforced, but if NULL is supplied, I 
> would like the operation to fail instead of advancing key to a new integer.

Just make the column "INT PRIMARY KEY NOT NULL" (note INT rather than
INTEGER). This way, it is not an alias for ROWID but a column in its own
right, and doesn't get assigned a value automatically.
--
Igor Tandetnik

___
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] autoincrement and primary key

[Igor Tandetnik]

On 5/20/2013 4:17 PM, Roman Fleysher wrote:

I would like to use INTEGER PRIMARY KEY, but I would like to disable its 
implicit AUTOINCREMENT feature. Namely, if INSERT specifies value of the 
column, I would like uniqueness to be enforced, but if NULL is supplied, I 
would like the operation to fail instead of advancing key to a new integer.


Just make the column "INT PRIMARY KEY NOT NULL" (note INT rather than 
INTEGER). This way, it is not an alias for ROWID but a column in its own 
right, and doesn't get assigned a value automatically.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] autoincrement and primary key

[Jean-Christophe Deschamps]

I would like to use INTEGER PRIMARY KEY, but I would like to disable 
its implicit AUTOINCREMENT feature. Namely, if INSERT specifies value 
of the column, I would like uniqueness to be enforced, but if NULL is 
supplied, I would like the operation to fail instead of advancing key 
to a new integer.


Switching declared type from INTEGER to INT should do what you want:

CREATE TABLE qqq (
  id INT PRIMARY KEY
); 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] autoincrement and primary key

[Roman Fleysher]

Dear SQLiters,

I would like to use INTEGER PRIMARY KEY, but I would like to disable its 
implicit AUTOINCREMENT feature. Namely, if INSERT specifies value of the 
column, I would like uniqueness to be enforced, but if NULL is supplied, I 
would like the operation to fail instead of advancing key to a new integer. 
Here is my failed attempt:

CREATE TABLE qqq (
  id INTEGER PRIMARY KEY   
);

CREATE TRIGGER qqqIdTrigger BEFORE INSERT ON qqq WHEN new.id IS NULL
  BEGIN 
select RAISE (FAIL, roma);
  END;

INSERT INTO qqq (id) VALUES (20);
INSERT INTO qqq (id) VALUES (NULL);

I tried typeof(new.id)='null' in the WHEN clause, both triggers register, but 
do not raise fail. Triggering when id is too high (WHEN new.id >20) works. I 
thought that when id being inserted is NULL it is autoincremented before 
trigger is called. But then, it must trigger the too-high version when passing 
threshold --- it did not.

I could replace INTEGER primary key by TEXT primary key and auto increment will 
go away. How can I keep integer?

Thank you,

Roman


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create Table oddity

[Simon Slavin]

On 20 May 2013, at 2:41pm, Keith Medcalf  wrote:

> It works quite fine:
> 
> sqlite> create table a(a,b text default (datetime()));
> sqlite> insert into a (a) values (1);
> sqlite> insert into a (a) values (2);
> sqlite> select * from a;
> 1|2013-05-20 13:27:30
> 2|2013-05-20 13:27:34

Interesting.  The documentation would appear to be wrong, or at least 
incomplete.  Thanks.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query help

[Keith Medcalf]

select * 
  from tab
 where not exists (select * 
 from tab as tab2 
where num1 = t1.num2);

As you see, there are many ways to obtain the result you want ...

This is a direct translation of the English "I want to identify each row where 
num2 does not have a matching num1".

The most efficient way to do this will depend on the characteristics of the 
data.  For big tables

Select *
  From tab
 Where num2 not in (select distinct num1
  From tab);

Will be the most efficient ...

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Paul Sanderson
> Sent: Monday, 20 May, 2013 08:00
> To: General Discussion of SQLite Database
> Subject: [sqlite] query help
> 
> I have a table of the form
> 
> create table tab (num int1 unique, num2, int)
> 
> for each row for num2 there is usually a matching num1. But not always.
> 
> I want to identify each row where num2 does not have a matching num1
> 
> example data might be
> 
> num1  num2
> 1  3
> 2  3
> 3  2
> 4  1
> 5  11
> 6  3
> 7  9
> 
> in this example my query would return rows 5 and 7 as there is no match on
> num1 for 11 and 9
> 
> Any ideas, cheers.
> ___
> 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] Create Table oddity

[Keith Medcalf]

It works quite fine:

sqlite> create table a(a,b text default (datetime()));
sqlite> insert into a (a) values (1);
sqlite> insert into a (a) values (2);
sqlite> select * from a;
1|2013-05-20 13:27:30
2|2013-05-20 13:27:34


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Monday, 20 May, 2013 07:17
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Create Table oddity
> 
> 
> On 20 May 2013, at 4:55am, Simon Slavin  wrote:
> 
> > I wonder if there's a difference between
> >
> > DEFAULT date('now')
> >
> > and
> >
> > DEFAULT (date('now'))
> 
> Bah.  Of course, you can't do either:
> 
> 
> 
> "An explicit DEFAULT clause may specify that the default value is NULL, a
> string constant, a blob constant, a signed-number, or any constant
> expression enclosed in parentheses. An explicit default value may also be
> one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or
> CURRENT_TIMESTAMP."
> 
> No variable expressions at all, with a non-standard hack to allow time-
> sensitive values.
> 
> Simon.
> ___
> 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] query help

[Igor Tandetnik]

On 5/20/2013 7:59 AM, Paul Sanderson wrote:

I have a table of the form

create table tab (num int1 unique, num2, int)

for each row for num2 there is usually a matching num1. But not always.

I want to identify each row where num2 does not have a matching num1


select * from tab where num2 not in (select num1 from tab);

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query help

[Jay A. Kreibich]

On Mon, May 20, 2013 at 12:59:45PM +0100, Paul Sanderson scratched on the wall:
> I have a table of the form
> 
> create table tab (num int1 unique, num2, int)
> 
> for each row for num2 there is usually a matching num1. But not always.
> 
> I want to identify each row where num2 does not have a matching num1
> 
> example data might be
> 
> num1  num2
> 1  3
> 2  3
> 3  2
> 4  1
> 5  11
> 6  3
> 7  9
> 
> in this example my query would return rows 5 and 7 as there is no match on
> num1 for 11 and 9

  Join the table to itself using an outer join on the condition that
  num2 == num1.  Look for rows where num1 is NULL, indicating no join
  was found.  Only works if num1 is never NULL in the DB.

  I need to run.  Perhaps someone else can provide an example if that's
  not making sense.

   -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] query help

[Michael Black]

I think this does what you want.  

create table tab (num1 int unique,num2 int);
insert into tab values(1,3);
insert into tab values(2,3);
insert into tab values(3,2);
insert into tab values(4,1);
insert into tab values(5,11);
insert into tab values(6,3);
insert into tab values(7,9);

sqlite> select t2.num1,t2.num2 from tab t2 where (select count(*) from tab
t1 where t2.num2 == t1.num1) == 0;
5|11
7|9

If you don't want to see num2 in the query answer:
sqlite> select num1 from (select t2.num1,t2.num2 from tab t2 where (select
count(*) from tab t1 where t2.num2 == t1.num1) == 0);
5
7





-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson
Sent: Monday, May 20, 2013 7:00 AM
To: General Discussion of SQLite Database
Subject: [sqlite] query help

I have a table of the form

create table tab (num int1 unique, num2, int)

for each row for num2 there is usually a matching num1. But not always.

I want to identify each row where num2 does not have a matching num1

example data might be

num1  num2
1  3
2  3
3  2
4  1
5  11
6  3
7  9

in this example my query would return rows 5 and 7 as there is no match on
num1 for 11 and 9

Any ideas, cheers.
___
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] query help

[Paul Sanderson]

I have a table of the form

create table tab (num int1 unique, num2, int)

for each row for num2 there is usually a matching num1. But not always.

I want to identify each row where num2 does not have a matching num1

example data might be

num1  num2
1  3
2  3
3  2
4  1
5  11
6  3
7  9

in this example my query would return rows 5 and 7 as there is no match on
num1 for 11 and 9

Any ideas, cheers.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create Table oddity

[Simon Slavin]

On 20 May 2013, at 4:55am, Simon Slavin  wrote:

> I wonder if there's a difference between
> 
> DEFAULT date('now')
> 
> and
> 
> DEFAULT (date('now'))

Bah.  Of course, you can't do either:



"An explicit DEFAULT clause may specify that the default value is NULL, a 
string constant, a blob constant, a signed-number, or any constant expression 
enclosed in parentheses. An explicit default value may also be one of the 
special case-independent keywords CURRENT_TIME, CURRENT_DATE or 
CURRENT_TIMESTAMP."

No variable expressions at all, with a non-standard hack to allow 
time-sensitive values.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users