Android.database.sqlite.sqliteexception: Near "on": Syntax Error (code 1)

This is the MySQL code I am using on the server to create a table... CREATE TABLE brand_names ( id int(7) unsigned NOT NULL AUTO_INCREMENT, brand_name varchar(255) NOT NULL

Solution 1:

The correct one is,

CREATETABLE brand_names (
    _id integerPRIMARY KEY AUTOINCREMENT,
    brand_name text NOTNULL,
    parent_company_id integerNOTNULLDEFAULT'0',
    last_modified_on datetime NOTNULLDEFAULTCURRENT_TIMESTAMP);

You are using ON UPDATE SET DEFAULT in the end which is wrong syntax.

The last_modified_on datetime will be automatically set to the current time by using DEFAULT CURRENT_TIMESTAMP.

What you have seen from the SQLite doc is totally different from what you want to achieve.

The following query will work assuming you have a parent table with id parent_company_id -

CREATETABLE brand_names(_id integerPRIMARY KEY AUTOINCREMENT,
brand_name text NOTNULL, parent_company_id integerNOTNULLDEFAULT'0', 
last_modified_on datetime NOTNULLREFERENCES parent(parent_company_id) 
ONUPDATESETDEFAULT);

This will update the parent_company_id (the parent key of the foreign key constraint) column of the parent record without breaking referential integrity between the two tables parent and brand_names.

An ON UPDATE action is only taken if the values of the parent key are modified so that the new parent key values are not equal to the old. So this is used to configure actions that take place when modifying the parent key values of existing rows (ON UPDATE). They are associated with each foreign key in an SQLite database.