Search code examples
pythonsqlsqliteconstraintsgenerated-columns

Set constraint based on another's cell value

I am making a text based RPG and I want to add a class to the player's stats, let's say for example Warrior or Hunter, I decided to use sqlite3 to store the data and I want it to assign a default value to the Power stat based on Class stat. This is not the code but what i want it to do:

if Class is Hunter:
 Power = 3
elif Class is Warrior:
 Power = 5
Solution

  • If your version of SQLite is 3.31.0+ you can define the column power as a GENERATED column:

    CREATE TABLE tablename (
  id INTEGER PRIMARY KEY,
  class TEXT,
  power INTEGER GENERATED ALWAYS AS (CASE class WHEN 'Hunter' THEN 3 WHEN 'Warrior' THEN 5 END)
);