Search code examples
sqlitetypesschema-design

Is there a prefered way to specify a text column in SQLite?

Since the SQLite engine will not truncate the data you store in a text column, is there any advantage in being specific with column sizes when you define your schema? Would anyone prefer this:

CREATE TABLE contact(
 id    INTEGER PRIMARY KEY, 
 name  VARCHAR(45),
 title VARCHAR(10)
);

over this:

CREATE TABLE contact(
 id    INTEGER PRIMARY KEY, 
 name  TEXT,
 title TEXT
);

Why?

Are there advantages to not being specific?

Solution

  • The advantage of using varchar(x) is that it is compatible with other database systems - if I remember correctly, TEXT isn't a standard SQL datatype.

    Other than being more standards-compliant, there is indeed no difference whether you use TEXT or VARCHAR. More info at http://sqlite.org/datatype3.html