Skip to content

ALTER TABLE

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

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:

  1. .query('ALTER TABLE... RENAME ...')
  2. .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:

  1. .query('ALTER TABLE... DROP CONSTRAINT constraint_1')
  2. .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()

Add a custom footer
Add a custom sidebar

Clone this wiki locally