[SQLite] Difference betwen my code and sqlite_master

I use (for example) this Code:

CREATE TABLE IF NOT EXISTS Heading (PNom VARCHAR NOT NULL, Nom VARCHAR NOT NULL, Wife VARCHAR, Enfant VARCHAR, " +_ "Sexe VARCHAR NOT NULL, Date_Birth VARCHAR NOT NULL, Pays VARCHAR NOT NULL, SentBy VARCHAR NOT NULL, " +_ "Date_In VARCHAR NOT NULL, Date_Out VARCHAR NOT NULL, Statut VARCHAR NOT NULL, ID INTEGER NOT NULL, PRIMARY KEY(ID));

and I get this in the sqlite_master TABLE:

CREATE TABLE Heading (PNom VARCHAR NOT NULL, Nom VARCHAR NOT NULL, Wife VARCHAR, Enfant VARCHAR, Sexe VARCHAR NOT NULL, Date_Birth VARCHAR NOT NULL, Pays VARCHAR NOT NULL, SentBy VARCHAR NOT NULL, Date_In VARCHAR NOT NULL, Date_Out VARCHAR NOT NULL, Statut VARCHAR NOT NULL, ID INTEGER NOT NULL, PRIMARY KEY(ID))

Note: these disappears in the operation
a. IF NOT EXISTS
b. ; (at the end of the line)

So, using the contents of sqlite_master is a bad idea ?

BTW: other CREATE TABLES strings in my project share the same behavior.

SQL Master doesn’t store your EXACT statement… it stores what the DB Engine derived from your statement
IF NOT EXISTS is not needed to “recreate” the table
and “;” at the end is not needed unless in a “script” to delineate statements

This is what I would expect…
Note : sometimes if you create tables with multiple Primary Keys, or multiple Unique constraints, what SQLite_Master will have is an optimized version of your original… but it is all good… so don’t worry about those things

Tadpole (my soon to be released SQLite manager for macOS and Windows) uses the contents of SQLite_Master extensively

Thank you Dave for your answer.

In my project, I create the data base file at application load (open) time. If I do not use IF NOT EXISTS I will fall into troubles (TABLE already exists).

CREATE TABLE shows an endig “;”.

That is why I asked my original question(s).

Thanks pal.

what you say it true…
how ever SQLite_master stores what is needed to RECREATE the table, as optimized by the engine when it was executed… meaning that when that statement was put into Master, your table did NOT exist, and this is what was used to create it.

So part of what you say is true. But personally (and I have been working in databases for a long time)… when I am creating a table I always check for existance first (SELECT count(*) from SQLite_master where type=‘table’ and tbl_name=‘mytable’)

and “;” is “optional” if there is only one statement… except I think in PRAGMA statements

Got it !