1

I am creating a sqlite3 table that accepts records from a server. There should be one date/text column that also has a datetime DEFAULT value, so I can sync a record which times differ from the server's record.

I found a solution on this forum from here. The problem is it gives me the following error on executing the table creation script: sqlite3.OperationalError: default value of column [updated_at] is not constant.

Table is created:

cur.execute('CREATE TABLE IF NOT EXISTS emp_tb(\
                    emp_id INTEGER PRIMARY KEY NOT NULL,\
                    emp_names TEXT NOT NULL,\
                    emp_number TEXT NOT NULL UNIQUE,\
                    ent_id INTEGER NOT NULL,\
                    active INTEGER NOT NULL DEFAULT "0",\
                    updated_at TEXT NULL DEFAULT (datetime("now", "localtime")),\
                    syncstatus INTEGER NOT NULL DEFAULT "0")')

Should I create a trigger? or How can I have a default value in format ("YYYY-MM-DD HH:MM:SS.SSS") in case the update misses a spot?

Hmerman6006
  • 1,622
  • 1
  • 20
  • 45
  • 2
    Try it with single quotes (`'`) on the `datetime` arguments. – DinoCoderSaurus Jan 08 '21 at 14:07
  • Thanks! Your suggested solution solved it. I also had to escape your suggested single quotes to make it work: ```updated_at TEXT NULL DEFAULT (datetime(\'now\', \'localtime\')),\```. If you make your comment the answer I will mark it as such. – Hmerman6006 Jan 08 '21 at 15:36

1 Answers1

3

Use single quotes (') for the datetime options. As mentioned in the comments, they will have to be escaped (because the query is delimited with single quotes).

DinoCoderSaurus
  • 6,110
  • 2
  • 10
  • 15