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
IF NOT EXISTS
; (at the end of the line)
So, using the contents of
sqlite_master is a bad idea ?
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).
http://sqlite.com/lang_createtable.html shows an endig “;”.
That is why I asked my original question(s).
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