How do you add a Column with a Case as the default in SQLite?-CodePudding

ALTER TABLE [table_name] ADD COLUMN alloc_strat varchar(25) NOT NULL 
 DEFAULT(CASE
    WHEN (shelf_life_unit = 'Months') THEN 'Min Remaining Shelf Life'
    ELSE 'FIFO')
;

This isn't working, Is there a way to do this in one sql statement

CodePudding user response：

In SQLite the added columns are not allowed to have expressions are their default.

The following illustrates the syntax of ALTER TABLE ADD COLUMN statement:

ALTER TABLE table_name ADD COLUMN column_definition; Code language: SQL (Structured Query Language) (sql) There are some restrictions on the new column:

The new column cannot have a UNIQUE or PRIMARY KEY constraint. If the new column has a NOT NULL constraint, you must specify a default value for the column other than a NULL value. The new column cannot have a default of CURRENT_TIMESTAMP, CURRENT_DATE, and CURRENT_TIME, or an expression. If the new column is a foreign key and the foreign key constraint check is enabled, the new column must accept a default value NULL.

Source