The CockroachDB Alter Table Part 2

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This is part two in a tutorial series explaining how to use the CockroachDB alter table statement. Part one of this series explained how to use the basic CockroachDB alter table statement and provided a list of commonly used sub commands used to make alterations to an existing table. Part two of this series will build one part one, including explaining the alter table configure zone and the alter table drop column functions. Therefore, it is highly recommended that part one of this series be completed, or reviewed if necessary, before proceeding.

Prerequisites

  • Completion of part one of the “CockroachDB Alter Table” tutorial series before proceeding with this second part.

  • CockroachDB must be properly installed and configured on the local device.

CockroachDB Alter Table

Part two will continue with examples for using the ALTER TABLE statement.

ALTER TABLE CONFIGURE ZONE

Following is the current zone configuration of the table:

1
2
3
4
5
6
7
8
9
     target     |              raw_config_sql
----------------+-------------------------------------------
  RANGE DEFAULT | ALTER RANGE DEFAULT CONFIGURE ZONE USING
                |     range_min_bytes = 134217728,
                |     range_max_bytes = 536870912,
                |     gc.ttlseconds = 90000,
                |     num_replicas = 1,
                |     constraints = '[]',
                |     lease_preferences = '[]'

Now execute the following ALTER TABLE statement to change the configuration:

1
2
3
4
5
ALTER TABLE employees
CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
CONFIGURE ZONE 1

TIME: 3.9799ms
1
2
3
4
5
6
7
8
9
      target      |               raw_config_sql
------------------+---------------------------------------------
  TABLE employees | ALTER TABLE employees CONFIGURE ZONE USING
                  |     range_min_bytes = 134217728,
                  |     range_max_bytes = 536870912,
                  |     gc.ttlseconds = 100000,
                  |     num_replicas = 5,
                  |     constraints = '[]',
                  |     lease_preferences = '[]'

Notice that the gc.replicas is now set to 100000 and the num_replicas is set to five.

ALTER TABLE DROP COLUMN

As shown in the following example, the ALTER TABLE DROP COLUMN command is used to delete a column from the table:

1
2
ALTER TABLE employees
DROP COLUMN gender;
1
2
3
4
5
6
7
8
  column_name |  data_type  | is_nullable | column_default | generation_expression |        indices         | is_hidden
--------------+-------------+-------------+----------------+-----------------------+------------------------+------------
  id          | INT8        |    FALSE    | NULL           |                       | {PRIMARY,unique_email} |   FALSE
  first_name  | VARCHAR(50) |    FALSE    | NULL           |                       | {}                     |   FALSE
  last_name   | VARCHAR(50) |    TRUE     | NULL           |                       | {}                     |   FALSE
  email       | STRING      |    TRUE     | NULL           |                       | {unique_email}         |   FALSE
  salary      | INT8        |    TRUE     | NULL           |                       | {}                     |   FALSE
(5 ROWS)

Note that the column gender has successfully been removed from the table.

ALTER TABLE DROP CONSTRAINT

This next example will demonstrate how to drop the constraint that was inserted into the salary column.

Execute the following statement to delete the constraint name check_salary:

1
2
ALTER TABLE employees
DROP CONSTRAINT check_salary;
1
2
3
4
5
6
SHOW CONSTRAINT FROM employees;
  TABLE_NAME | constraint_name | constraint_type |       details        | validated
-------------+-----------------+-----------------+----------------------+------------
  employees  | PRIMARY         | PRIMARY KEY     | PRIMARY KEY (id ASC) |   TRUE
  employees  | unique_email    | UNIQUE          | UNIQUE (email ASC)   |   TRUE
(2 ROWS)

Note that the constraint has been successfully removed from the table.

ALTER TABLE EXPERIMENTAL_AUDIT

The EXPERIMENTAL_AUDIT command is used to turn on the audit logging that contains detailed information about the queries that are executed.

The following statement will create an audit log for the employees table:

1
2
ALTER TABLE employees
EXPERIMENTAL_AUDIT SET READ WRITE;

Note: The logs in the default storage of logs are in the same directory as other logs.

ALTER TABLE RENAME COLUMN

The following statement will change the name of the salary column to income.

1
2
ALTER TABLE EMPLOYEES
RENAME COLUMN salary TO income;
1
2
3
4
5
6
7
8
9
SHOW COLUMNS FROM employees;
  column_name |  data_type  | is_nullable | column_default | generation_expression |        indices         | is_hidden
--------------+-------------+-------------+----------------+-----------------------+------------------------+------------
  id          | INT8        |    FALSE    | NULL           |                       | {PRIMARY,unique_email} |   FALSE
  first_name  | VARCHAR(50) |    FALSE    | NULL           |                       | {}                     |   FALSE
  last_name   | VARCHAR(50) |    TRUE     | NULL           |                       | {}                     |   FALSE
  email       | STRING      |    TRUE     | NULL           |                       | {unique_email}         |   FALSE
  income      | INT8        |    TRUE     | NULL           |                       | {}                     |   FALSE
(5 ROWS)

Notice that the column name has now been successfully changed.

ALTER TABLE RENAME CONSTRAINT

This following example shows how to change the name of the constraint unique_email to `uq_employee_email:

1
2
ALTER TABLE employees
RENAME CONSTRAINT unique_email TO uq_employee_email;
1
2
3
4
5
6
SHOW CONSTRAINT FROM employees;
  TABLE_NAME |  constraint_name  | constraint_type |       details        | validated
-------------+-------------------+-----------------+----------------------+------------
  employees  | PRIMARY           | PRIMARY KEY     | PRIMARY KEY (id ASC) |   TRUE
  employees  | uq_employee_email | UNIQUE          | UNIQUE (email ASC)   |   TRUE
(2 ROWS)

Again note the name of the constraint has been successfully changed.

ALTER TABLE RENAME TABLE

This next example demonstrates how to change the name of the table from employees to staff:

1
2
ALTER TABLE employees
RENAME TO staff;
1
2
3
4
5
6
7
8
9
10
11
SHOW TABLES;
          TABLE_NAME
------------------------------
  promo_codes
  rides
  staff
  user_promo_codes
  users
  vehicle_location_histories
  vehicles
(7 ROWS)

Note that employees has been changed to staff on the table list.

ALTER TABLE SPLIT AT

The following ALTER TABLE SPLIT AT statement is used to automatically split the range if the limit exceeds a predetermined size:

1
2
3
4
5
6
ALTER TABLE staff SPLIT AT VALUES ('1'), ('2');
      KEY      |    pretty     |       split_enforced_until
---------------+---------------+-----------------------------------
  \304\211\211 | /TABLE/60/1/1 | 2262-04-11 23:47:16.854776+00:00
  \304\211\212 | /TABLE/60/1/2 | 2262-04-11 23:47:16.854776+00:00
(2 ROWS)

ALTER TABLE UNSPLIT AT

Here, in the following example, the UNSPLIT AT command can be used to remove the created split:

1
2
3
4
5
6
ALTER TABLE staff UNSPLIT AT VALUES ('1'), ('2');
      KEY      |    pretty
---------------+----------------
  \304\211\211 | /TABLE/60/1/1
  \304\211\212 | /TABLE/60/1/2
(2 ROWS)

ALTER TABLE VALIDATE CONSTRAINT

This section will explain the VALIDATE CONSTRAINT statement used to add a new constraint and to check the values already in the table.

Execute the following command to add a constraint on the column income:

1
2
ALTER TABLE staff
ADD CONSTRAINT chk_employee_income CHECK (income > 0);

Now execute the following command to confirm the records on the table match the constraint:

1
2
ALTER TABLE staff
VALIDATE CONSTRAINT chk_employee_income;

Conclusion

This was part two in a tutorial series explaining how use to the CockroachDB alter table statement and built on the functions explained in part one of this series. Part two covered how to use the Alter Table Configure Zone, the Drop Column and the Drop Constraint statements. The article then explained how to execute the Alter Table Experimental_Audit statement to create an audit log and how to use the Rename Column and Rename Constraint statements. Part two then covered how to execute the Rename Table, Split At, Unsplit At and the Alter Table Validate Constraint functions. Remember that the logs in the default storage of logs are in the same directory as other logs. Refer to part one of this tutorial series on how use to the CockroachDB alter table statement if needed.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.