Arwid ThornströmandGitHub
3a90bb553c
* add product description tracking table * added description tracking table * create product_description_updated table * updated with indexes * fixed table with indexes * fix * correct migration number
47 lines
2.3 KiB
SQL
47 lines
2.3 KiB
SQL
DROP TABLE IF EXISTS product_descriptions_updated CASCADE;
|
|
|
|
CREATE TABLE product_descriptions_updated (
|
|
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
productid INT NOT NULL,
|
|
locale_id INT NOT NULL,
|
|
product_type VARCHAR(50) NOT NULL,
|
|
updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
-- Foreign key constraint for productid
|
|
-- This constraint creates a foreign key relationship between the 'productid' column in the current table
|
|
-- and the 'productid' column in the 'product-products' table. The ON DELETE CASCADE clause means that if a product is
|
|
-- deleted from the 'product-products' table, all corresponding rows in this table will also be automatically deleted.
|
|
CONSTRAINT fk_product_descriptions_updated_product
|
|
FOREIGN KEY (productid) REFERENCES "product-products"(productid)
|
|
ON DELETE CASCADE,
|
|
|
|
-- Foreign key constraint for locale_id
|
|
-- This constraint creates a foreign key relationship between the 'locale_id' column in the current table
|
|
-- and the 'id' column in the 'locales' table. The ON DELETE CASCADE clause means that if a locale is
|
|
-- deleted from the 'locales' table, all corresponding rows in this table will also be automatically deleted.
|
|
CONSTRAINT fk_product_descriptions_updated_locale
|
|
FOREIGN KEY (locale_id) REFERENCES locales(id)
|
|
ON DELETE CASCADE,
|
|
|
|
-- Check constraint for product_type
|
|
-- This constraint ensures that the product_type column only contains allowed values
|
|
-- It restricts the product_type to be one of: 'wallpaper', 'canvas', or 'poster'
|
|
-- Any attempt to insert or update a record with a different product_type will fail
|
|
CONSTRAINT chk_product_descriptions_updated_product_type
|
|
CHECK (product_type IN ('wallpaper', 'canvas', 'poster'))
|
|
);
|
|
|
|
-- Additional indexes for query performance
|
|
CREATE INDEX idx_product_descriptions_updated_productid
|
|
ON product_descriptions_updated (productid);
|
|
|
|
CREATE INDEX idx_product_descriptions_updated_locale_id
|
|
ON product_descriptions_updated (locale_id);
|
|
|
|
CREATE INDEX idx_product_descriptions_updated_updated
|
|
ON product_descriptions_updated (updated DESC);
|
|
|
|
-- Unique index to ensure one record per product, locale, and product type combination
|
|
CREATE UNIQUE INDEX unique_product_locale_product_type
|
|
ON product_descriptions_updated (productid, locale_id, product_type);
|