Re: [sqlite] Violating Primary key Constraint

[Kees Nuyt]

On Tue, 25 Nov 2008 11:47:10 +0530, "Satish"
<[EMAIL PROTECTED]> wrote in General Discussion of SQLite
Database :

>Hi!
>You Said me to create the field as "INT PRIMARY KEY NOT NULL" instead of
>"INTEGER PRIMARY KEY NOT NULL".Even we spell differently they are working
>same.it is also auto incrementing.
>
>sqlite> create table dummytable (recid integer PRIMARY KEY NOT NULL,
>label text);
>sqlite> insert into dummytable (label) VALUES ('foo');
>sqlite> select * from dummytable;
>1|foo
>sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
>text);
>sqlite> insert into secondtable (label) VALUES ('foo');
>1|foo
>I am not getting any constraint failed error

Hm, I do get it using the command line tool as downloaded a
few days ago: sqlite_version():3.6.6.1 

create table table1 (
recid integer PRIMARY KEY NOT NULL, label text);
create table table2 (
recid INT PRIMARY KEY NOT NULL, label text);
.bail OFF
insert into table1 (label) VALUES ('foo');
select * from table1;
1|foo
insert into table2 (label) VALUES ('foo');
SQL error near line 5: table2.recid may not be NULL
select * from table2;


Which version do you use?

>Regards,
>Satish.G
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Violating Primary key Constraint

[Rob Sciuk]

> Hi All!
>
>   I have created a table in sqlite.Upto my knowledge a column which is
> declared as primary key will not accept null and even if I don't give any
> value to that field an error occurs that violating the constraint
> 
> For Example
> 
> Create table emp(empno integer PRIMARY KEY,...,...)
> 
> . Even if I give u a NULL as a value to the field that is declared
> as primary key .it is accepting
> 
> . Even if I Don't give any value to the field that is declared as
> primary key it is auto incrementing. Instead of showing the error 
> constraint violated it is  auto increment that field value(I did not specify 
> the 
> column to auto increment).
> 
>

Satish,

If you don't want the magical autoincrement feature try defining your 
table using an alternate syntax:

create table emp (
empno integer not null,
...
primary key (empno)
) ;

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

Re: [sqlite] Violating Primary key Constraint

[Igor Tandetnik]

"Satish" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> You Said me to create the field as "INT PRIMARY KEY NOT NULL" instead
> of "INTEGER PRIMARY KEY NOT NULL".Even we spell differently they are
> working
> same.it is also auto incrementing.
>
> sqlite> create table dummytable (recid integer PRIMARY KEY NOT NULL,
> label text);
> sqlite> insert into dummytable (label) VALUES ('foo');
> sqlite> select * from dummytable;
> 1|foo
> sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL,
> label
> text);
> sqlite> insert into secondtable (label) VALUES ('foo');
> 1|foo

Are you suggesting the last "1|foo" output was produced by "insert into 
secondtable" statement? With all due respect, I find it hard to believe.

Anyway, the statements you show work for me as expected, in that the 
last insert statement does produce an error.

Igor Tandetnik 



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

Re: [sqlite] Violating Primary key Constraint

[Ian Walters]

I copied and pasted the lines from your email into SQLite and got a
different response from SQLite than you claim you got.

sqlite3
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
   ...> text);
sqlite> insert into secondtable (label) VALUES ('foo');
SQL error: secondtable.recid may not be NULL
sqlite>

Check you used INT, not INTEGER.  Check you used NOT NULL.  Check your
version of SQLite.

If your output below really did come from SQLite and you are using a
recent version, then I doubt I can help.

--
Ian

Satish wrote:
> Hi!
> You Said me to create the field as "INT PRIMARY KEY NOT NULL" instead of
> "INTEGER PRIMARY KEY NOT NULL".Even we spell differently they are working
> same.it is also auto incrementing.
> 
> sqlite> create table dummytable (recid integer PRIMARY KEY NOT NULL,
> label text);
> sqlite> insert into dummytable (label) VALUES ('foo');
> sqlite> select * from dummytable;
> 1|foo
> sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
> text);
> sqlite> insert into secondtable (label) VALUES ('foo');
> 1|foo
> I am not getting any constraint failed error
> 
> Regards,
> Satish.G
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Ian Walters
> Sent: Tuesday, November 25, 2008 11:17 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Violating Primary key Constraint
> 
> Hi Satish,
> 
> I just re-read your original email and Igor's response seemed
> appropriate.  The original email read as if you were reporting a problem
> rather than asking a question.
> 
> As for you question:
> 
> sqlite> create table dummytable (recid integer PRIMARY KEY NOT NULL,
> label text);
> sqlite> insert into dummytable (label) VALUES ('foo');
> sqlite> select * from dummytable;
> 1|foo
> sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
> text);
> sqlite> insert into secondtable (label) VALUES ('foo');
> SQL error: secondtable.recid may not be NULL
> 
> --
> Ian
> 
> 
> 
> Satish wrote:
>> Hi Igor!
>>
>> Thanks for the reply.The thing I need is I don't want the field to be
>> auto incremented it should show me the error that constraint violated even
>> if I give NULL or empty value.how can I do this.Don't send me links which
>> doesn't have matter at all.First try to understand the problem and then
> give
>> me reply.
>>
>> Regards,
>> Satish.G
>>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
>> Sent: Tuesday, November 25, 2008 10:59 AM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Violating Primary key Constraint
>>
>> "Satish" <[EMAIL PROTECTED]> wrote in
>> message news:[EMAIL PROTECTED]
>>>  I have created a table in sqlite.Upto my knowledge a column which is
>>> declared as primary key will not accept null and even if I don't give
>>> any value to that field an error occurs that violating the constraint
>>>
>>> For Example
>>>
>>> Create table emp(empno integer PRIMARY KEY,...,...)
>>>
>>> . Even if I give u a NULL as a value to the field that is
>>> declared as primary key .it is accepting
>> http://sqlite.org/autoinc.html
> 
> ___
> 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] Violating Primary key Constraint

[Satish]

Hi!
You Said me to create the field as "INT PRIMARY KEY NOT NULL" instead of
"INTEGER PRIMARY KEY NOT NULL".Even we spell differently they are working
same.it is also auto incrementing.

sqlite> create table dummytable (recid integer PRIMARY KEY NOT NULL,
label text);
sqlite> insert into dummytable (label) VALUES ('foo');
sqlite> select * from dummytable;
1|foo
sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
text);
sqlite> insert into secondtable (label) VALUES ('foo');
1|foo
I am not getting any constraint failed error

Regards,
Satish.G

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ian Walters
Sent: Tuesday, November 25, 2008 11:17 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Violating Primary key Constraint

Hi Satish,

I just re-read your original email and Igor's response seemed
appropriate.  The original email read as if you were reporting a problem
rather than asking a question.

As for you question:

sqlite> create table dummytable (recid integer PRIMARY KEY NOT NULL,
label text);
sqlite> insert into dummytable (label) VALUES ('foo');
sqlite> select * from dummytable;
1|foo
sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
text);
sqlite> insert into secondtable (label) VALUES ('foo');
SQL error: secondtable.recid may not be NULL

--
Ian



Satish wrote:
> Hi Igor!
> 
> Thanks for the reply.The thing I need is I don't want the field to be
> auto incremented it should show me the error that constraint violated even
> if I give NULL or empty value.how can I do this.Don't send me links which
> doesn't have matter at all.First try to understand the problem and then
give
> me reply.
> 
> Regards,
> Satish.G
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
> Sent: Tuesday, November 25, 2008 10:59 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Violating Primary key Constraint
> 
> "Satish" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>>  I have created a table in sqlite.Upto my knowledge a column which is
>> declared as primary key will not accept null and even if I don't give
>> any value to that field an error occurs that violating the constraint
>>
>> For Example
>>
>> Create table emp(empno integer PRIMARY KEY,...,...)
>>
>> . Even if I give u a NULL as a value to the field that is
>> declared as primary key .it is accepting
> 
> http://sqlite.org/autoinc.html

___
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] Violating Primary key Constraint

[Satish]

Hi Igor!
  
 You Said me to create the field as "INT PRIMARY KEY" instead of
"INTEGER PRIMARY KEY".Even we spell differently they are working same.it is
also auto incrementing.

Regards,
Satish.G

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
Sent: Tuesday, November 25, 2008 11:13 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Violating Primary key Constraint

"Satish" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
>Thanks for the reply.The thing I need is I don't want the field to
> be
> auto incremented

Spell the field in some way other than precisely "INTEGER PRIMARY KEY". 
E.g. "INT PRIMARY KEY" would work as you expect.

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] Violating Primary key Constraint

[Ian Walters]

Hi Satish,

I just re-read your original email and Igor's response seemed
appropriate.  The original email read as if you were reporting a problem
rather than asking a question.

As for you question:

sqlite> create table dummytable (recid integer PRIMARY KEY NOT NULL,
label text);
sqlite> insert into dummytable (label) VALUES ('foo');
sqlite> select * from dummytable;
1|foo
sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
text);
sqlite> insert into secondtable (label) VALUES ('foo');
SQL error: secondtable.recid may not be NULL

--
Ian



Satish wrote:
> Hi Igor!
> 
> Thanks for the reply.The thing I need is I don't want the field to be
> auto incremented it should show me the error that constraint violated even
> if I give NULL or empty value.how can I do this.Don't send me links which
> doesn't have matter at all.First try to understand the problem and then give
> me reply.
> 
> Regards,
> Satish.G
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
> Sent: Tuesday, November 25, 2008 10:59 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Violating Primary key Constraint
> 
> "Satish" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>>  I have created a table in sqlite.Upto my knowledge a column which is
>> declared as primary key will not accept null and even if I don't give
>> any value to that field an error occurs that violating the constraint
>>
>> For Example
>>
>> Create table emp(empno integer PRIMARY KEY,...,...)
>>
>> . Even if I give u a NULL as a value to the field that is
>> declared as primary key .it is accepting
> 
> http://sqlite.org/autoinc.html

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

Re: [sqlite] Violating Primary key Constraint

[Igor Tandetnik]

"Satish" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
>Thanks for the reply.The thing I need is I don't want the field to
> be
> auto incremented

Spell the field in some way other than precisely "INTEGER PRIMARY KEY". 
E.g. "INT PRIMARY KEY" would work as you expect.

Igor Tandetnik



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

Re: [sqlite] Violating Primary key Constraint

[Satish]

Hi Igor!

Thanks for the reply.The thing I need is I don't want the field to be
auto incremented it should show me the error that constraint violated even
if I give NULL or empty value.how can I do this.Don't send me links which
doesn't have matter at all.First try to understand the problem and then give
me reply.

Regards,
Satish.G

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
Sent: Tuesday, November 25, 2008 10:59 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Violating Primary key Constraint

"Satish" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
>  I have created a table in sqlite.Upto my knowledge a column which is
> declared as primary key will not accept null and even if I don't give
> any value to that field an error occurs that violating the constraint
>
> For Example
>
> Create table emp(empno integer PRIMARY KEY,...,...)
>
> . Even if I give u a NULL as a value to the field that is
> declared as primary key .it is accepting

http://sqlite.org/autoinc.html
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925 



___
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] Violating Primary key Constraint

[P Kishor]

On 11/24/08, Satish <[EMAIL PROTECTED]> wrote:
> Hi All!
>
>
>
>   I have created a table in sqlite.Upto my knowledge a column which is
>  declared as primary key will not accept null and even if I don't give any
>  value to that field an error occurs that violating the constraint
>
>  For Example
>
>  Create table emp(empno integer PRIMARY KEY,...,...)
>
>  . Even if I give u a NULL as a value to the field that is declared
>  as primary key .it is accepting
>
>  . Even if I Don't give any value to the field that is declared as
>  primary key it is auto incrementing. Instead of showing the error constraint
>  violated it is  auto increment that field value(I did not specify the column
>  to auto increment).
>
>
>

from the docs 

"According to the SQL standard, PRIMARY KEY should imply NOT NULL.
Unfortunately, due to a long-standing coding oversight, this is not
the case in SQLite. SQLite allows NULL values in a PRIMARY KEY column.
We could change SQLite to conform to the standard (and we might do so
in the future), but by the time the oversight was discovered, SQLite
was in such wide use that we feared breaking legacy code if we fixed
the problem. So for now we have chosen to continue allowing NULLs in
PRIMARY KEY columns. Developers should be aware, however, that we may
change SQLite to conform to the SQL standard in future and should
design new programs accordingly."


>
>
>  Regards,
>
>  Satish.G
>
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Violating Primary key Constraint

[Igor Tandetnik]

"Satish" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
>  I have created a table in sqlite.Upto my knowledge a column which is
> declared as primary key will not accept null and even if I don't give
> any value to that field an error occurs that violating the constraint
>
> For Example
>
> Create table emp(empno integer PRIMARY KEY,...,...)
>
> . Even if I give u a NULL as a value to the field that is
> declared as primary key .it is accepting

http://sqlite.org/autoinc.html
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925 



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

[sqlite] Violating Primary key Constraint

[Satish]

Hi All!

 

  I have created a table in sqlite.Upto my knowledge a column which is
declared as primary key will not accept null and even if I don't give any
value to that field an error occurs that violating the constraint 

For Example

Create table emp(empno integer PRIMARY KEY,...,...)

. Even if I give u a NULL as a value to the field that is declared
as primary key .it is accepting

. Even if I Don't give any value to the field that is declared as
primary key it is auto incrementing. Instead of showing the error constraint
violated it is  auto increment that field value(I did not specify the column
to auto increment).

 

 

Regards,

Satish.G

 

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