samedi 9 janvier 2016

SQLite BINARY column cause error with DEFAULT values

It's been a while since I used SQL, and I am relearning a lot. However, I cannot figure out why my DEFAULT values are causing errors in my SQL with SQLite. I have created 4 tables, and most of them use DEFAULT values of some sort; however, this table requires DEFAULT BOOLEAN values. For some reason, that causes errors, and I cannot figure out why.

I tried to use a 3-bit flag column, but it looks like that is not supported by SQLite, so I am now using 3 booleans.

DDL

Look at the last 4 columns in the SQL (i.e. is_usdr_created, is_continuous_play, is_random_play, repeat_all_play)

CREATE TABLE IF NOT EXISTS playlists (
  playlist_id        INT  NOT NULL,
  category_id        INT  NOT NULL,
  name               TEXT NOT NULL CHECK (trim(name) != ''),
  video_count        INT CHECK (video_count > 0),
  is_user_created    BOOLEAN DEFAULT (0),
  is_continuous_play BOOLEAN DEFAULT (1),
  is_random_play     BOOLEAN DEFAULT (0),
  repeat_all_play    BOOLEAN DEFAULT (0),

  PRIMARY KEY (playlist_id),
  FOREIGN KEY (category_id) REFERENCES categories (category_id)
);

Error

[1] [SQLITE_ERROR] SQL error or missing database (near "DEFAULT": syntax error)

If I remove all the DEFAULT keywords and values, then everything works fine. Why is that? Can't Booleans get default values as one of my booleans needs a default of true.

Aucun commentaire:

Enregistrer un commentaire