default issue

(1) By anonymous on 2022-09-05 16:53:19 [link] [source]

When I enter data through my program, the defaults are applied. But when I add a record via the SqLiteStudio 3.3.3, the default does not get applied.

What am I doing wrong?

(2) By Larry Brasfield (larrybr) on 2022-09-05 18:57:16 in reply to 1 [link] [source]

... What am I doing wrong?

At least, you are providing too little information for anybody to help.

The SQLite library uses default field values upon insertion when they are not provided in an INSERT statement. I would guess that SqLiteStudio's GUI does not provide a way to specify no value and does something simple-minded such as using an empty string where a little datum box has been left "empty".

Whatever SqLiteStudio does, it is not part of the SQLite project. Hence, your question would perhaps fare better in a forum or support channel for that other application.

(3.1) By Chris Locke (chrisjlocke1) on 2022-09-05 22:14:45 edited from 3.0 in reply to 1 [source]

When you add a record using SQLite Studio, does it log the SQL it used to insert the record?

Maybe 'my program' is applying the defaults, not the database? Can you supply the schema for the table? Does it have default constraints? (well, not default constraints as in constraints added by default, but constraints using the DEFAULT keyword....)

(4.1) Originally by anonymous with edits by Stephan Beal (stephan) on 2022-09-05 23:00:09 from 4.0 in reply to 3.1 [link] [source]

CREATE TABLE tblMailingList (
    iMailingListID INTEGER PRIMARY KEY AUTOINCREMENT
                           UNIQUE
                           NOT NULL,
    sFirstName     TEXT,
    sLastName      TEXT,
    sSpouseName    TEXT,
    sAddressLine1  TEXT,
    sAddressLine2  TEXT,
    sCity          TEXT,
    sState         TEXT,
    sPostalCode    TEXT,
    sLandLinePhone TEXT,
    sMobilePhone   TEXT,
    sEmailAddress  TEXT,
    bLabelPrinted  BOOLEAN DEFAULT (0),
    bToBePrinted   BOOLEAN DEFAULT (0),
    bBlankRecord   BOOLEAN DEFAULT (0) 
);



((admin: changed to plain text for readability))

(5) By Chris Locke (chrisjlocke1) on 2022-09-05 23:53:45 in reply to 4.1 [link] [source]

So ignoring the fact that 'boolean' isn't a SQLite data type, what happens when you insert your record? What value is in bLabelPrinted? Null?

(6) By anonymous on 2022-09-06 00:02:13 in reply to 5 [link] [source]

Yes.

Should I change it to integer?

(7) By anonymous on 2022-09-06 00:04:13 in reply to 5 [link] [source]

Only with the insert via my application does the default kick in. In the Sql Studio, the default is ignored and a null is there.

(8) By Stephan Beal (stephan) on 2022-09-06 00:09:39 in reply to 7 [link] [source]

Only with the insert via my application does the default kick in. In the Sql Studio, the default is ignored and a null is there.

Then, as Larry suggested, the issue is with Sql Studio (a separate project) and you'll need to ask them about it. Presumably they have contact info in the "about" dialog box.

(9.1) By Keith Medcalf (kmedcalf) on 2022-09-06 00:37:48 edited from 9.0 in reply to 7 [link] [source]

Obviously SQLiteStudio is sending a null rather than omitting the field entirely.

If you wish to have SQLite3 apply a default such that the field is never null, then declare the column thusly:

col <datatype phrase> not null on conflict replace default <default value>

This means that the column col is not allowed to be null. If it is attempted to insert (or update to) a null then that null is replaced with the default value.

So, if the column is boolean and can only contain 1 or 0, and a null should be 0, then:

col boolean integer not null on conflict replace default 0 check (col in (0,1))

Note that your table has the same column as INTEGER PRIMARY KEY and UNIQUE. It should be one or the other, not both. Do you have a reason for using AUTOINCREMENT or did it just sound neat?

(10) By anonymous on 2022-09-06 02:23:35 in reply to 9.1 [link] [source]

Thank you for the assist.

I need autoincrement because there is a relational database design in play with foreign keys, etc.

(11) By Gunter Hick (gunter_hick) on 2022-09-06 06:05:31 in reply to 10 [link] [source]

You might like to check that assertion. See https://sqlite.org/autoinc.html

Do you need rowid values to be unique over the lifetime of the database, as opposed to unique for all existing rows (which is already guaranteed by INTEGER PRIMARY KEY)? Ask yourself why.

Do you need rowid values to be monotonically increasing? Ask yourself why.

It is a common pitfall among designers new to SQLite to think they need AUTOINCREMENT; especially of the schema was orginally designed for a different database that actually did require it.