What would you expect to happen

Databases
1

While this scenario makes no sense from a business point of view, the fact is it can be created, and is technically valid

CREATE TABLE myTable (
column1 Integer NOT NULL COLLATE NOCASE ,
PRIMARY KEY(column1 AUTOINCREMENT),
UNIQUE(column1))

If you had a 3rd party Manager… and told it to ADD a row… what would you EXPECT the response to be

FYI… most I have tested this with emit an error, usually one that makes no sense to the user…

What I would expect, it that it should add a row, with a NULL value (since it can plainly determine the field is a PK Integer Auto Inc
for that matter, the same should happen even if there are other fields…
That would be my expected results from an ADD ROW
for Update Row, I’d expect that app to allow me to enter ANY integer, and then at commit time issue an error if there was a conflict between the value I entered, and the existing data or constraints.

What would YOU expect

2

SQLite?

3

that if you created that table like that and then did

insert into mytable ( column1 ) values ( null )

you’d get a row inserted, no error, and column1 on the first insert would be 1 (integer autoincrement)
and subsequent inserts using the same syntax (inserting null) would also generate increasing values in column1
further if you did

insert into mytable ( column1 ) values ( 3 ) insert into mytable ( column1 ) values ( 3 )
the first would work, and the second would generate an error since it would fail the unique constraint

4

Using Dave’s approach, Valentina gives me an error when I try to insert a row. But if I rewrite it like this, it works just fine:

CREATE TABLE myTable (
column1 Integer NOT NULL COLLATE NOCASE
PRIMARY KEY
AUTOINCREMENT UNIQUE)
5

Yes, SQLite…
And I am not talking about SQL you manually write… That is up to you to insure meets your intentions

I am talking about 3rd party SQLite Managers that have a WIZARD to add/edit rows in a table

My thoughts are

  • If the field is a PK Integer AutoInc, then default to NULL, if the user enters a value, check its constraint
  • Otherwise let the user enter what they want (since SQLite really is typeless), and let the database check any constraints such as NOT NULL, UNIQUE etc.

Seems SQLite datatypes are a guideline, not a “rule” as it would be in anyother database engine.

6

Dave, just so you didn’t miss it, I got your original point and rewriting the CREATE statement as above bypassed the issue in Valentina without changing how the table was defined.

7

But that is not my point, my point is more accuratly… “What would you expect an Add/Edit Row WIZARD to do?”

A few I’ve seen (including the one from SQLabs)… generate this statement

INSERT INTO myTABLE () VALUES()

which clearly is not correct

8

I’d expect it to behave as I described because that IS what sqlite does (other db’s will do something else but I tried this in sqlite already)

9

Yes Norman… I understand that is the correct expectations of MANUALLY created code…
That is NOT the opinion I am seeking…

What would you expect an Add/Edit Row WIZARD to do?"

10

Oh, I get it.

I’d expect

INSERT INTO myTable DEFAULT VALUES;
11

[quote=326528:@Kem Tekinay]Oh, I get it.

I’d expect

INSERT INTO myTable DEFAULT VALUES; [/quote]
I was not even aware of that syntax…

FYI… the reason I am asking… is because I too am working on an SQLite Manager (Tadpole… which is a nod to TOAD :slight_smile: )
And I want to insure mine doesn’t have the same pitfalls as others I have seen, but still provides the flexibility that would be expected. (SQLManager by SQLabs bombs so often it is not even funny :frowning: ) and SQLMan (which I had been using for years) is way out of date (2007)

12

SQLManager has a habit of locking up and just croaking on me, when testing with the demo. Certain alter table statements were completely crashing it right off the bat.

Let me know when yours is out. :wink:

13 
insert into default values

would be fine since you have no other choice if there’s no immediate opportunity for a user to specify columns and values to use

14

Not discouraging Dave, but Valentina Studio is awesome, stable, and free.

15

Dave, I don’t know if you’re planning to support alternate SQL engines, so I’ll point out that Postgres has a very nice option that, unfortunately, isn’t available in SQLite:

INSERT INTO my_table (col1, col2, col3) VALUES (DEFAULT, 1, DEFAULT)
16

no worries Kem… but two things… I do not like Valentina (I tried it… not to my liking… my opinon) and based on the quality of most of the 3rd party SQLite managers I have seen… some do some things well, others to other things well, but none do everything that I want “well”… So there is still room for alternatives…

and based on my research.
A field that is PK Integer AutoInc… must either INSERT as NULL, or a non-existing value, or Added as a non-existing value, or left out of the UPdate statement, and if a value IS specified it MUST be an INTEGER
Any other fields are fair game, unless they have NOT NULL and/or UNIQUE constraints, in which case they must honor those, but the data content can in fact be “anything”