H2 Database - Edit

Donald Green Donald Green
Feb 3, 2022 - 02:03
0 22
H2 Database - Edit

ALTER is a command used to change the structure of a table by adding various clauses to the alter command . Based on the scenario, we need to add an appropriate clause to the alter command. In this chapter, we will discuss various scenarios for changing a command.

Edit Table Add

Alter Table Add is a command used to add a new column to a table along with the corresponding data type. This command commits a transaction on this connection.

Syntax

Following is the general syntax of the Alter Table Add command.

ALTER TABLE [ IF EXISTS ] tableName ADD [ COLUMN ]
{ [ IF NOT EXISTS ] columnDefinition [ { BEFORE | AFTER } columnName ]
   | ( { columnDefinition } [,...] ) }

example

In this example, we will add a new start_date column to the tutorials_tbl table . The data type for start_date is Date. Following is the request to add a new column.

ALTER TABLE tutorials_tbl ADD start_date DATE ;

The above query results in the following output.

(6) rows effected

Edit Table Add Constraint

Edit table add constraint is a command used to add various constraints to a table such as primary key, foreign key, not null, etc.

The required indexes are created automatically if they do not already exist. It is not possible to disable unique constraint checking. This command commits an open transaction on this connection.

Syntax

Following is the general syntax of the Alter table constraint command.

ALTER TABLE [ IF EXISTS ] tableName ADD constraint [ CHECK | NOCHECK]

example

In this example, we will add a primary key constraint (tutorials_tbl_pk) to the column ID of the tutorials_tbl table using the following query.

ALTER TABLE tutorials_tbl ADD CONSTRAINT tutorials_tbl_pk PRIMARYKEY ( id );

The above query results in the following output.

(6) row(s) effected

Alter Table Rename Constraint

This command is used to rename the constraint name of a particular relationship table. This command commits an open transaction on this connection.

Syntax

Following is the general syntax of the Edit Table Rename Constraint command.

ALTER TABLE [ IF EXISTS ] tableName RENAME oldConstraintName TO newConstraintName

When using this syntax, ensure that the old constraint name must exist with the appropriate column.

example

In this example, we will change the primary key constraint name of the tutorials_tbl table from tutorials_tbl_pk to tutorials_tbl_pk_constraint . Below is the query for this.

ALTER TABLE tutorials_tbl RENAME CONSTRAINT
tutorials_tbl_pk TO tutorials_tbl_pk_constraint ;

The above query results in the following output.

(1) row(s) effected

Edit Table Edit Column

This command is used to change the structure and properties of a particular table column. Changing properties means changing the data type of a column, renaming a column, changing an identifier value, or changing selectivity.

Syntax

Following is the general syntax of the Edit Table command.

ALTER TABLE [ IF EXISTS ] tableName ALTER COLUMN columnName
{ { dataType [ DEFAULT expression ] [ [ NOT ] NULL ] [ AUTO_INCREMENT | IDENTITY] }
| { RENAME TO name }
| { RESTART WITH long }
| { SELECTIVITY int }
| { SET DEFAULT expression }
| { SET NULL }
| { SET NOT NULL } }

In the above syntax −

  • RESTART - the command changes the next value of an auto-increment column.

  • SELECTIVITY - the command sets the selectivity (1-100) for the column. Based on the selectivity value, we can display the value of the column.

  • SET DEFAULT - Changes the default value for a column.

  • SET NULL - Sets the column to NULL resolution.

  • SET NOT NULL - Sets the column's NOT NULL permission.

RESTART - the command changes the next value of an auto-increment column.

SELECTIVITY - the command sets the selectivity (1-100) for the column. Based on the selectivity value, we can display the value of the column.

SET DEFAULT - Changes the default value for a column.

SET NULL - Sets the column to NULL resolution.

SET NOT NULL - Sets the column's NOT NULL permission.

example

In this example, we will rename the table column tutorials_tbl from title to Tutorial_Title using the following query.

ALTER TABLE tutorials_tbl ALTER COLUMN title RENAME TO tutorial_title;

The above query results in the following output.

(0) row(s) effected

Similarly, we can execute various scripts with the ALTER command.