schema_migration.sql en SQL

La reconstrucción de tabla: la única forma de agregar una restricción que ALTER no puede.

-- Agregar una columna NOT NULL y un CHECK a una tabla viva. SQLite no
-- puede incorporarlos con ALTER, así que la tabla se reconstruye con los datos.

PRAGMA foreign_keys = OFF;

BEGIN;

CREATE TABLE payments_new (
    id INTEGER PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE SET NULL,
    amount REAL NOT NULL CHECK (amount > 0),
    currency TEXT NOT NULL CHECK (LENGTH(currency) = 3),
    method TEXT NOT NULL DEFAULT 'card'
        CHECK (method IN ('card', 'transfer', 'credit')),
    paid_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO payments_new (id, order_id, amount, currency, method, paid_at)
SELECT
    id,
    order_id,
    amount,
    UPPER(COALESCE(NULLIF(TRIM(currency), ''), 'USD')),
    CASE
        WHEN method IN ('card', 'transfer', 'credit') THEN method
        ELSE 'card'
    END,
    COALESCE(paid_at, CURRENT_TIMESTAMP)
FROM payments
WHERE amount > 0;

DROP TABLE payments;

ALTER TABLE payments_new RENAME TO payments;

CREATE INDEX payments_by_order
    ON payments (order_id);

CREATE INDEX payments_by_day
    ON payments (paid_at);

PRAGMA foreign_key_check;

COMMIT;

PRAGMA foreign_keys = ON;

SELECT
    method,
    currency,
    COUNT(*) AS payments,
    ROUND(SUM(amount), 2) AS collected
FROM payments
GROUP BY method, currency
ORDER BY collected DESC;

Cómo funciona

1. Las claves foráneas se apagan fuera de la transacción y se encienden al final.
2. Crea la tabla nueva, copia las filas, borra la vieja y renombra en su lugar.
3. foreign_key_check antes de COMMIT es lo que vuelve esto seguro de correr.

Palabras clave y builtins usados aquí

• ALTER
• AS
• BEGIN
• BY
• CASE
• CHECK
• COALESCE
• COMMIT
• COUNT
• CREATE
• CURRENT_TIMESTAMP
• DEFAULT
• DELETE
• DESC
• DROP
• ELSE
• END
• FROM
• GROUP
• IN
• INDEX
• INSERT
• INTEGER
• INTO
• KEY
• LENGTH
• NOT
• NULL
• NULLIF
• OFF
• ON
• ORDER
• PRIMARY
• REAL
• REFERENCES
• RENAME
• SELECT
• SET
• SUM
• TABLE
• TEXT
• THEN
• TO
• TRIM
• UPPER
• WHEN
• WHERE
• method

El intento, en números

Líneas

55

Caracteres a escribir

1224

Tokens

227

Ritmo de tres estrellas

100 tpm

Al ritmo de tres estrellas de 100 tokens por minuto, este intento toma unos 136 segundos.

Escribe este fragmento

Paso 1 de 2 en Bis; paso 15 de 16 en Esquema y restricciones.

← Anterior Siguiente →