CODEWITHSUNDEEP
sqlite
Miguel Moura
Miguel Moura

Reputation: 39474

Create Blob column in Sqlite

I am trying to create a table in SQLite with blob type column (Content):

create table [Files]
(
    Id int identity not null
        constraint PK_File_Id primary key,
    MimeType nvarchar (400) not null,
    Content varbinary (max) null
       constraint DF_File_Content default (0x),
);

However the following is not being accepted:

    Content varbinary (max) null
       constraint DF_File_Content default (0x),

Why?

Upvotes: 0

Views: 8880

Answers (2)

dan04
dan04

Reputation: 91189

See the syntax reference for the CREATE TABLE statement and data types. A type name can include numbers in parentheses (which are ignored), but not the word “MAX”.

It looks like you're trying to use MS SQL Server syntax, and there are several errors in your code:

  • As mentioned above, (max) is not accepted as part of a type name. Since value lengths are unconstrained by default, simply omit it.
  • varbinary gives the column “numeric affinity”. While such a column can store a blob, you'll probably want to declare it as blob instead.
  • 0x is not a valid blob literal. The correct way to write an empty blob is x''.
  • identity is called autoincrement. And in order to use it, the type name must be integer rather than int. The not null is redundant: If you try to insert a null value into such a column, you'll get the auto-incremented ROWID instead.
    • Note: If you simply need Id to have unique values at any given time and don't care if previously-deleted values get re-used, then you can simply declare the column as integer primary key, and inserting null will fill in the column with an unused integer. The autoincrement keyword prevents the re-use of ROWIDs over the lifetime of the database, more closely matching the semantics of MS SQL's identity keyword. See the discussion at the link above.
  • While it's syntactically legal to declare a name for a column constraint, it's not necessary.

Try this:

create table [Files]
(
    Id integer primary key autoincrement,
    MimeType nvarchar (400) not null,
    Content blob null default (x'')
);

Note that this does not enforce a length limit on a MimeType column. If you need to, add the constraint check (length(MimeType) <= 400).

Upvotes: 1

varro
varro

Reputation: 2482

"Max" is the name of a standard SQLite3 function, so is not available as part of a type name.

Upvotes: 1

Related Questions