Skip to content

ALTER TABLE

Jump to bottom Edit New page
Oxford Harrison edited this page Nov 10, 2024 · 20 revisions

Manage Basic Details

Rename table:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE public.table_1
    RENAME TO table_1_new`,
    { desc: 'Alter description' }
);
// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => schema.name('table_1_new'),
    { desc: 'Alter description' }
);

Alter deeply:

// Function-based syntax
const savepoint = await client.database('public').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' }
);

Note that the equivalent SQL syntax would require two client.query() calls:

  1. ALTER TABLE... RENAME ...
  2. ALTER TABLE... RENAME COLUMN ..., RENAME CONSTRAINT ...

Manage Columns

Add columns:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE public.table_1
        ADD col_3 varchar UNIQUE,
        ADD COLUMN col_4 varchar`,
    { desc: 'Alter description' }
);
// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.column({ name: 'col_3', type: 'varchar', uniqueKey: true });
        schema.column({ name: 'col_4', type: 'varchar' });
    },
    { desc: 'Alter description' }
);

To add an IF NOT EXISTS flag to each ADD COLUMN operation, set options.asserts === false.

database.alterTable(..., { desc: 'Alter description', asserts: false });

Drop columns:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE public.table_1
        DROP col_3,
        DROP COLUMN col_4`,
    { desc: 'Alter description' }
);
// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_3', false);
        schema.column('col_4', false);
    },
    { desc: 'Alter description' }
);

Tip

Postgres Extras

To add an IF EXISTS flag to each DROP COLUMN operation, set options.asserts === false.

database.alterTable(..., { desc: 'Alter description', asserts: false });

To add a CASCADE flag to each DROP COLUMN operation, set options.cascades === true.

database.alterTable(..., { desc: 'Alter description', cascades: true });

Manage Constraints

Add constraints (auto-named):

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE public.table_1
        ADD CONSTRAINT
            PRIMARY KEY (col_1),
        ADD CONSTRAINT 
            FOREIGN KEY (col_2)
            REFERENCES public.table_2 (col_1),
        ADD CONSTRAINT
            UNIQUE (col_3),
        ADD CONSTRAINT
            CHECK (col_4 > 2)`,
    { desc: 'Alter description' }
);
// (b): Function-based syntax 1
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_1').primaryKey(true);
        schema.column('col_2').foreignKey({
            targetTable: ['public', '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 savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.constraint({
            type: 'PRIMARY_KEY',
            columns: ['col_1']
        });
        schema.constraint({
            type: 'FOREIGN_KEY',
            columns: ['col_2']
            targetTable: ['public', '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 that while single-column constraints may be defined at the table level, they are moved into their respective columns under the hood by Linked QL; given a consistent way to access them whether syntax (b) or syntax (c).

Note too that 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
const savepoint = await client.query(
    `ALTER TABLE public.table_1
        ADD CONSTRAINT constraint_1
            PRIMARY KEY (col_1),
        ADD CONSTRAINT constraint_2 
            FOREIGN KEY (col_2)
            REFERENCES public.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 savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_1').primaryKey({
            name: 'constraint_1',
        });
        schema.column('col_2').foreignKey({
            targetTable: ['public', '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 savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.constraint({
            type: 'PRIMARY_KEY',
            columns: ['col_1']
            name: 'constraint_1',
        });
        schema.constraint({
            type: 'FOREIGN_KEY',
            columns: ['col_2']
            targetTable: ['public', '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, that while single-column constraints may be defined at the table level, they are moved into their respective columns under the hood by Linked QL; given a consistent way to access them whether syntax (b) or syntax (c).

Add constraints (multi-column):

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE public.table_1
        ADD CONSTRAINT constraint_1
            PRIMARY KEY (col_1, col_2),
        ADD CONSTRAINT constraint_2 
            FOREIGN KEY (col_2, col_3)
            REFERENCES public.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 savepoint = await client.database('public').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: ['public', '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 that multi-column constraints can only be defined as above: at the table level.

Drop constraints:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE public.table_1
        DROP CONSTRAINT constraint_4`,
    { desc: 'Alter description' }
);
// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.constraint('constraint_4', false);
    },
    { desc: 'Alter description' }
);

Tip

Postgres Extras

To add an IF EXISTS flag to each DROP CONSTRAINT operation, set options.asserts === false.

database.alterTable(..., { desc: 'Alter description', asserts: false });

To add a CASCADE flag to each DROP CONSTRAINT operation, set options.cascades === true.

database.alterTable(..., { desc: 'Alter description', cascades: true });

Manage Constraints (fine-grained):

// Function-based syntax
const savepoint = await client.database('public').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(['public', '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' }
);

Note that the equivalent SQL syntax would require two client.query() calls each - dropping and recreating the respective constraints:

  1. ALTER TABLE... DROP CONSTRAINT constraint_1
  2. ALTER TABLE... ADD CONSTRAINT constraint_1...
Add a custom footer
Add a custom sidebar
Clone this wiki locally