erd.sketch

SQL 迁移：两个版本之间的 ALTER

左边是原来的结构，右边是变更后的。下面是编辑器给出的、把前者变成后者的语句——真实输出，而不是文档里的示例。

原来v1.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(120) NOT NULL,
    created_at TIMESTAMPTZ
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total NUMERIC(12,2) NOT NULL
);
之后v2.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(120),
    created_at TIMESTAMPTZ NOT NULL,
    CONSTRAINT uq_users_email UNIQUE (email)
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total NUMERIC(12,2) NOT NULL,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE INDEX idx_orders_user ON orders (user_id);
输出PostgreSQL
ALTER TABLE users ADD COLUMN phone VARCHAR(120);
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ALTER COLUMN created_at SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

CREATE INDEX idx_orders_user ON orders (user_id);

ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;
打开编辑器

脚本留在浏览器里：解析和生成都在您的机器上进行，不会上传到服务器。

同一处改动在不同方言里的写法

PostgreSQLALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
MySQL / MariaDBALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL;
SQL ServerALTER TABLE users ALTER COLUMN email NVARCHAR(255) NOT NULL;
OracleALTER TABLE users MODIFY (email VARCHAR2(255));

相关页面

Lint

其他数据库

PostgreSQLMySQLSQLiteSQL ServerOracle