Thanks, and sorry on a good day I knew that. My real issue is:
CREATE TABLE "Test" ("A" VARCHAR(36) COLLATE NOCASE, "B" VARCHAR(36) COLLATE
NOCASE, PRIMARY KEY("A", "B"))
INSERT INTO Test("A", "B") VALUES ('1', '1') --> No Error
INSERT INTO Test("A", "B") VALUES ('1', '1') --> Duplicate Error
INSERT INTO Test("A", "B") VALUES ('2', NULL) --> No Error
INSERT INTO Test("A", "B") VALUES ('2', NULL) --> No Error (It inserts
another copy)
I tried to see what MS SQL would do but it does not allow creation of the
tables primary key with any NULL segments.
Does that mean I need to do my own check for an existing record instead of
using insert or replace?
Thanks,
Matt Froncek
QODBC Development Support / FLEXquarters Solutions Consultant
QODBC Driver for QuickBooks - Unleash your data at www.qodbc.com
-----Original Message-----
From: Dan Kennedy [mailto:[EMAIL PROTECTED]
Sent: Friday, September 02, 2005 11:40 PM
To: [email protected]
Subject: Re: [sqlite] Null values in Primary Key
NULL = NULL is false. It's an SQL thing - go figure. Try:
SELECT * FROM test WHERE b IS NULL;
http://www.sqlite.org/nulls.html
--- Matt Froncek <[EMAIL PROTECTED]> wrote:
> I am using SQLCrypt and it returns SQLite Library number 3002002. I have
> tried with Encryption on and off.
>
> Why can't I find a record saved with NULL in one of the fields of the
> primary key?
>
> CREATE TABLE "Test" ("A" VARCHAR(36) COLLATE NOCASE, "B" VARCHAR(36)
COLLATE
> NOCASE, PRIMARY KEY("A", "B"))
>
> INSERT INTO Test("A", "B") VALUES ('1', '1')
> INSERT INTO Test("A", "B") VALUES ('2', NULL)
>
> SELECT * From Test WHERE B = NULL --> Returns no records
> SELECT * From Test WHERE B = '' --> Returns no records
> SELECT * From Test --> Returns both records
> SELECT * From Test WHERE A = '2' --> Returns the second record
>
> Matt Froncek
> QODBC Development Support / FLEXquarters Solutions Consultant
> QODBC Driver for QuickBooks - Unleash your data at www.qodbc.com
>
>
>
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
