Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to set a default value to a TEXT column in Sqlite?

I have a simple table. I'm trying to put a default value to a TEXT column. Here is the table query:

"CREATE TABLE book(_id INTEGER PRIMARY KEY AUTOINCREMENT, book_id TEXT NOT NULL DEFAULT '0', book_name TEXT NOT NULL);"   

It creates the table but the problem occurs when i try to insert a data. I was only trying with giving a book name to book_name, as i expected that the book_id would have a default value 0 to the column. But it doesn't and it adds the null value, so the row doesn't get inserted. I have also tried with this query:

"CREATE TABLE book(_id INTEGER PRIMARY KEY AUTOINCREMENT, book_id TEXT NOT NULL DEFAULT \'0\', book_name TEXT NOT NULL);"     

But the problem remains the same. I have searched the stack overflow, and got some answers but they are old and not working for me right now. So has something changed on how to set the default value to a TEXT column in sqlite. Thanks in advance :)

EDIT
Here is the insert statement:

database.insert("book", null, cv);    

Here cv is the object of ContentValues which contains only the value for the column book_name.

like image 878
Abir Hasan Avatar asked Jan 21 '16 05:01

Abir Hasan


People also ask

How do I set a default column value?

In Object Explorer, right-click the table with columns for which you want to change the scale and select Design. Select the column for which you want to specify a default value. In the Column Properties tab, enter the new default value in the Default Value or Binding property.

Can I use varchar in SQLite?

SQLite does not enforce the length of a VARCHAR. You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there. And it will keep all 500-million characters intact. Your content is never truncated.

What is the default value of column?

Default values can be NULL, or they can be a value that matches the data type of the column (number, text, date, for example).

How do I create a column primary key in SQLite?

Syntax. The syntax to add a primary key to a table in SQLite is: PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE table_name RENAME TO old_table; CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ...


2 Answers

You can specify a default value for the column when you create the table. (It doesn't appear as though you can add a default using an ALTER statement, so you'll have to recreate your table.)

CREATE TABLE your_table_name
(MainContactName TEXT NOT NULL DEFAULT '')

For Example,

CREATE TABLE book(_id INTEGER PRIMARY KEY AUTOINCREMENT,book TEXT DEFAULT "abc");

now see that , default value is set to "abc"

Check sqllite documentation.

like image 184
Amit Vaghela Avatar answered Sep 29 '22 17:09

Amit Vaghela


To alter table,

sqlitedbInstance.execSQL("alter table myTable add column Address TEXT DEFAULT 'ABC' ");
like image 41
LincyTonita Avatar answered Sep 29 '22 18:09

LincyTonita