Query

app.db — product_variants

SQL

CREATE TABLE product_variants  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id INTEGER NOT NULL,
  sku TEXT,
  color TEXT,
  size TEXT,
  price REAL,
  stock_quantity INTEGER DEFAULT 0,
  is_available INTEGER DEFAULT 1,
  created_at TEXT NOT NULL,
  updated_at TEXT,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
product_id INTEGER Rename | Drop
sku TEXT ✓ Rename | Drop
color TEXT ✓ Rename | Drop
size TEXT ✓ Rename | Drop
price REAL ✓ Rename | Drop
stock_quantity INTEGER ✓ Rename | Drop
is_available INTEGER ✓ Rename | Drop
created_at TEXT Rename | Drop
updated_at TEXT ✓ Rename | Drop

Foreign Keys

Column Destination
product_id products.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_variants_color color SQL 
CREATE INDEX idx_variants_color
ON product_variants(color)
Drop
idx_variants_product product_id SQL 
CREATE INDEX idx_variants_product
ON product_variants(product_id)
Drop
idx_variants_product_id product_id SQL 
CREATE INDEX idx_variants_product_id
ON product_variants(product_id)
Drop
idx_variants_size size SQL 
CREATE INDEX idx_variants_size
ON product_variants(size)
Drop
idx_variants_sku sku SQL 
CREATE INDEX idx_variants_sku
ON product_variants(sku)
Drop
idx_variants_stock
  • stock_quantity
  • is_available
 SQL 
CREATE INDEX idx_variants_stock
ON product_variants(stock_quantity, is_available)
Drop