-- Disable foreign key enforcement
PRAGMA foreign_keys = OFF;
-- Create a temporary table to hold the existing data
CREATE TEMPORARY TABLE temp_stocks AS SELECT * FROM stocks;
-- Drop the existing stocks table
DROP TABLE stocks;
-- Create a new stocks table with the additional uniqueStockId column
CREATE TABLE stocks (
id INTEGER PRIMARY KEY,
conversionFactor REAL NOT NULL,
createdBy INTEGER NOT NULL,
createdAt REAL NOT NULL,
updatedBy INTEGER,
updatedAt REAL,
branch INTEGER NOT NULL,
product INTEGER NOT NULL,
stock INTEGER NOT NULL,
uniqueStockId TEXT UNIQUE, -- New column with UNIQUE constraint
FOREIGN KEY (branch) REFERENCES branches (id),
FOREIGN KEY (product) REFERENCES products (id),
FOREIGN KEY (createdBy) REFERENCES users (id),
FOREIGN KEY (updatedBy) REFERENCES users (id)
);
-- Populate the new stocks table with data from the temporary table, computing the uniqueStockId
INSERT INTO stocks (id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, uniqueStockId)
SELECT id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, CAST(branch || '-' || product AS TEXT) AS uniqueStockId
FROM temp_stocks;
PRAGMA foreign_keys = ON;
-- Disable foreign key enforcement
PRAGMA foreign_keys = OFF;
-- Create a temporary table to hold the existing data
CREATE TEMPORARY TABLE temp_stocks AS SELECT * FROM stocks;
-- Drop the existing stocks table
DROP TABLE stocks;
-- Create a new stocks table with the additional uniqueStockId column
CREATE TABLE stocks (
id INTEGER PRIMARY KEY,
conversionFactor REAL NOT NULL,
createdBy INTEGER NOT NULL,
createdAt REAL NOT NULL,
updatedBy INTEGER,
updatedAt REAL,
branch INTEGER NOT NULL,
product INTEGER NOT NULL,
stock INTEGER NOT NULL,
uniqueStockId TEXT UNIQUE, -- New column with UNIQUE constraint
FOREIGN KEY (branch) REFERENCES branches (id),
FOREIGN KEY (product) REFERENCES products (id),
FOREIGN KEY (createdBy) REFERENCES users (id),
FOREIGN KEY (updatedBy) REFERENCES users (id)
);
-- Populate the new stocks table with data from the temporary table, computing the uniqueStockId
INSERT INTO stocks (id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, uniqueStockId)
SELECT id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, CAST(branch || '-' || product AS TEXT) AS uniqueStockId
FROM temp_stocks;
PRAGMA foreign_keys = ON;
M
A
S
V
T
Y
T
A
C
Gwrangler 3.28.1
Mapping SQL input into an array of statements
D
K
Ɽ
I
H
