ALTER TABLE

DOCS • LANG • ALTER

See APIS ➞ client.query(), database.alterTable()

See related ➞ ALTER DATABASE ➞ Manage Tables

Manage Basic Details

Rename table:

// (a): SQL syntax
await client.query(
    `ALTER TABLE database_1.table_1
        RENAME TO table_1_new`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => schema.name('table_1_new'),
    { desc: 'Alter description' }
);

Note

While the default function-based syntax may read "alter table", you can imply the "view" kind by setting options.kind === 'view':

client.alterTable(..., { desc: 'Alter description', kind: 'view' });

Alter with an EXISTS check (PostgreSQL):

// (a): SQL syntax
await client.query(
    `ALTER TABLE IF EXISTS database_1.table_1
        RENAME TO table_1_new`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => schema.name('table_1_new'),
    { desc: 'Alter description', ifExists: true }
);

Alter deeply:

// Function-based syntax
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        schema.name('table_1_new');
        schema.column('col_1').name('col_1_new');
        schema.constraint('constraint_1').name('constraint_1_new');
    },
    { desc: 'Alter description' }
);

Tip

The equivalent SQL syntax via client.query() would otherwise be:

.query('ALTER TABLE... RENAME ...')
.query('ALTER TABLE... RENAME COLUMN ..., RENAME CONSTRAINT ...')

Manage Columns

See related ➞ ALTER COLUMN

Add columns:

// (a): SQL syntax
await client.query(
    `ALTER TABLE database_1.table_1
        ADD col_3 varchar UNIQUE,
        ADD COLUMN col_4 varchar`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        schema.column({ name: 'col_3', type: 'varchar', uniqueKey: true });
        schema.column({ name: 'col_4', type: 'varchar' });
    },
    { desc: 'Alter description' }
);

Note

Where the column implied by name already exists, the column is modified with the diff between the existing schema and the new schema.

Drop columns:

// (a): SQL syntax
await client.query(
    `ALTER TABLE database_1.table_1
        DROP col_3,
        DROP COLUMN col_4`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_3', false);
        schema.column('col_4', false);
    },
    { desc: 'Alter description' }
);

Tip

PostgreSQL:

To add a CASCADE or RESTRICT flag to each DROP COLUMN operation, use options.cascadeRule.

database.alterTable(..., { desc: 'Alter description', cascadeRule: 'CASCADE' });

Manage Constraints

See related ➞ ALTER CONSTRAINT

Add constraints (auto-named):

// (a): SQL syntax
await client.query(
    `ALTER TABLE database_1.table_1
        ADD CONSTRAINT
            PRIMARY KEY (col_1),
        ADD CONSTRAINT 
            FOREIGN KEY (col_2)
            REFERENCES database_1.table_2 (col_1),
        ADD CONSTRAINT
            UNIQUE (col_3),
        ADD CONSTRAINT
            CHECK (col_4 > 2)`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax 1
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_1').primaryKey(true);
        schema.column('col_2').foreignKey({
            targetTable: ['database_1', 'table_2'],
            targetColumns: ['col_1']
        });
        schema.column('col_3').uniqueKey(true);
        schema.column('col_4').check({ expr: { greaterThan: ['col_4', 4] } });
    },
    { desc: 'Alter description' }
);

// (c): Function-based syntax 2
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        schema.constraint({
            type: 'PRIMARY_KEY',
            columns: ['col_1']
        });
        schema.constraint({
            type: 'FOREIGN_KEY',
            columns: ['col_2'],
            targetTable: ['database_1', 'table_2'],
            targetColumns: ['col_1']
        });
        schema.constraint({
            type: 'UNIQUE_KEY',
            columns: ['col_3']
        });
        schema.constraint({
            type: 'CHECK',
            expr: { greaterThan: ['col_4', 4] }
        });
    },
    { desc: 'Alter description' }
);

Note

Where the constraint implied by name already exists, the constraint is modified with the diff between the existing schema and the new schema.

Note

While single-column constraints may be defined at the table level, they are moved into their respective columns under the hood by Linked QL. This gives us a consistent way to access a constraint whether defined via syntax (b) or syntax (c):

const checkConstraint = schema.column('col_4').check();
const fkConstraint = schema.column('col_2').foreignKey();

Yet, these can always be accessed by name at the table level:

const checkConstraint = schema.constraint(constraintName);
const fkConstraint = schema.constraint(constraintName);

Note

In all cases above, each constraint is automatically assigned a random name by Linked QL; useful for when they need to be accessed subsequently.

Add constraints (explicitly-named, recommended):

// (a): SQL syntax
await client.query(
    `ALTER TABLE database_1.table_1
        ADD CONSTRAINT constraint_1
            PRIMARY KEY (col_1),
        ADD CONSTRAINT constraint_2 
            FOREIGN KEY (col_2)
            REFERENCES database_1.table_2 (col_1),
        ADD CONSTRAINT constraint_3
            UNIQUE (col_3),
        ADD CONSTRAINT constraint_4
            CHECK (col_4 > 2)`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax 1
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_1').primaryKey({
            name: 'constraint_1',
        });
        schema.column('col_2').foreignKey({
            targetTable: ['database_1', 'table_2'],
            targetColumns: ['col_1'],
            name: 'constraint_2'
        });
        schema.column('col_3').uniqueKey({
            name: 'constraint_3'
        });
        schema.column('col_4').check({
            expr: { greaterThan: ['col_4', 4] },
            name: 'constraint_4'
        });
    },
    { desc: 'Alter description' }
);

// (c): Function-based syntax 2
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        schema.constraint({
            type: 'PRIMARY_KEY',
            columns: ['col_1'],
            name: 'constraint_1'
        });
        schema.constraint({
            type: 'FOREIGN_KEY',
            columns: ['col_2'],
            targetTable: ['database_1', 'table_2'],
            targetColumns: ['col_1'],
            name: 'constraint_2'
        });
        schema.constraint({
            type: 'UNIQUE_KEY',
            columns: ['col_3'],
            name: 'constraint_3'
        });
        schema.constraint({
            type: 'CHECK',
            expr: { greaterThan: ['col_4', 4] },
            name: 'constraint_4'
        });
    },
    { desc: 'Alter description' }
);

Note

As before, while single-column constraints may be defined at the table level, they are moved into their respective columns under the hood by Linked QL. This gives us a consistent way to access a constraint whether defined via syntax (b) or syntax (c):

const checkConstraint = schema.column('col_4').check();
const fkConstraint = schema.column('col_2').foreignKey();

Yet, these can always be accessed by name at the table level:

const checkConstraint = schema.constraint('constraint_2');
const fkConstraint = schema.constraint('constraint_4');

Add constraints (multi-column):

// (a): SQL syntax
await client.query(
    `ALTER TABLE database_1.table_1
        ADD CONSTRAINT constraint_1
            PRIMARY KEY (col_1, col_2),
        ADD CONSTRAINT constraint_2 
            FOREIGN KEY (col_2, col_3)
            REFERENCES database_1.table_2 (col_1, col_2),
        ADD CONSTRAINT constraint_3
            UNIQUE (col_3, col_4),
        ADD CONSTRAINT constraint_4
            CHECK (col_4 > col_5)`,
    { desc: 'Alter description' }
);

// (c): Function-based syntax
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        schema.constraint({
            type: 'PRIMARY_KEY',
            columns: ['col_1', 'col_2'],
            name: 'constraint_1'
        });
        schema.constraint({
            type: 'FOREIGN_KEY',
            columns: ['col_2', 'col_3'],
            targetTable: ['database_1', 'table_2'],
            targetColumns: ['col_1', 'col_2'],
            name: 'constraint_2'
        });
        schema.constraint({
            type: 'UNIQUE_KEY',
            columns: ['col_3', 'col_4'],
            name: 'constraint_3'
        });
        schema.constraint({
            type: 'CHECK',
            expr: { greaterThan: ['col_4', 'col_5'] },
            name: 'constraint_4'
        });
    },
    { desc: 'Alter description' }
);

Note

As before, where the constraint implied by name already exists, the constraint is modified with the diff between the existing schema and the new schema.

Note

Multi-column constraints can only be defined as above: at the table level.

Drop constraints:

// (a): SQL syntax
await client.query(
    `ALTER TABLE database_1.table_1
        DROP CONSTRAINT constraint_4`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        schema.constraint('constraint_4', false);
    },
    { desc: 'Alter description' }
);

Tip

PostgreSQL:

To add a CASCADE or RESTRICT flag to each DROP CONSTRAINT operation, use options.cascadeRule.

database.alterTable(..., { desc: 'Alter description', cascadeRule: 'CASCADE' });

Manage Constraints (fine-grained):

// Function-based syntax
const table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        // Rename primary key
        schema.column('col_1').primaryKey().name('pk_1_new');
        // Change a foreign key's "targetTable" and "targetColumn" references
        schema.column('col_2').foreignKey()
            .targetTable(['database_1', 'table_10'])
            .columns('col_30');
        // Change a constraint's "columns" list
        schema.constraint('constraint_1').columns(['col_1', 'col_2', 'col_3']);
        // Change a check constraint's expression
        schema.column('col_4').check().expr({ greaterThan: ['col_4', 40] });
    },
    { desc: 'Alter description' }
);

Tip

This granular level of modification is acheived in SQL by dropping and recreating the respective constraints, and the equivalent SQL syntax via client.query() otherwise would be:

.query('ALTER TABLE... DROP CONSTRAINT constraint_1')
.query('ALTER TABLE... ADD CONSTRAINT constraint_1...')

The `RETURNING` clause

Return the resulting table schema:

// (a): SQL syntax
const schema = await client.query(
    `ALTER TABLE database_1.table_1
        RENAME TO table_1_new
    RETURNING SCHEMA`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const schema = await database.alterTable(
    'table_1',
    (schema) => {
        schema.name('table_1_new');
    },
    { desc: 'Alter description', returning: 'schema' }
);

See related ➞ table.schema()

Return the associated savepoint instance:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE database_1.table_1
        RENAME TO table_1_new
    RETURNING SAVEPOINT`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await database.alterTable(
    'table_1',
    (schema) => {
        schema.name('table_1_new');
    },
    { desc: 'Alter description', returning: 'savepoint' }
);

See related ➞ database.savepoint()

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ALTER TABLE

Manage Basic Details

Manage Columns

Manage Constraints

The `RETURNING` clause

Clone this wiki locally

ALTER TABLE

Manage Basic Details

Manage Columns

Manage Constraints

The RETURNING clause

Clone this wiki locally

The `RETURNING` clause