The CockroachDB Alter Table Part 1

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

Introduction

The ALTER TABLE statement in CockroachDB is used to make alterations to an existing table in Cockroach database. Using the CockroachDB alter table is a fast and easy method of changing data in an already existing table to add a new column to an existing table, add a constraint, drop the column’s constraint and many other functions. This tutorial will explain how to use the CockroachDB alter table statement and provide working examples with the commonly used sub commands.

Prerequisites

  • CockroachDB must be properly installed and configure on the local system to use the CockroachDB alter table statement.

CockroachDB Alter Table

The ALTER TABLE function is used to make an alteration on an existing table. The statement must be followed by sub commands and these sub commands can be used as a combination in a single ALTER TABLE statement.

Following is an example of the ALTER TABLE statement syntax:

1
ALTER TABLE TABLE_NAME [ subcommand ]

A lists of commonly used sub commands follows:

SUBCOMMANDDESCRIPTION
ADD COLUMNused to add a column
ADD CONSTRAINTused to add a constraint on a table
ALTER COLUMNused to change a column or drop the column’s constraint
ALTER TYPEused to change the columns data type
CONFIGURE ZONEused to configure replication zones
DROP COLUMNused to delete a column on a table
DROP CONSTRAINTused to delete the constraint on the table
EXPERIMENTAL_AUDITused to enable or disable the audit logs
PARTITION BYused to partition, re-partition or un-partition a table
RENAME COLUMNused to change the name of a column
RENAME CONSTRAINTused to change the column constraint
RENAME TABLEused to change the name of a table
SPLIT ATused to make a force range split at the row of the table
UNSPLIT ATused to delete the force range split
VALIDATE CONSTRAINTcheck if the value matches the constraint on a table

CockroachDB Alter Table Examples

The section will cover how to create a table and add a new column.

First, execute the following to create a table:

1
2
3
4
5
6
7
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50),
gender VARCHAR (10),
email VARCHAR (50)
);

Now execute the SHOW COLUMNS statement to view the details of the new table:

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} |   FALSE
  first_name  | VARCHAR(50) |    TRUE     | NULL           |                       | {}        |   FALSE
  last_name   | VARCHAR(50) |    TRUE     | NULL           |                       | {}        |   FALSE
  gender      | VARCHAR(10) |    TRUE     | NULL           |                       | {}        |   FALSE
  email       | VARCHAR(50) |    TRUE     | NULL           |                       | {}        |   FALSE
(5 ROWS)

Next, insert the following records in the table.

1
2
3
INSERT INTO employees ( id, first_name, last_name, gender, email )
VALUES ( '1', 'Darren', 'Dodd', 'male', 'dodd@microsoft.com' ),
( '2', 'Greta' , 'Duncan', 'male', 'duncan@gmail.com' );

With the records inserted, the table should resemble this:

1
2
3
4
  id | first_name | last_name | gender |       email
-----+------------+-----------+--------+---------------------
   1 | Darren     | Dodd      | male   | dodd@microsoft.com
   2 | Greta      | Duncan    | male   | duncan@gmail.com

Examples of the commonly used sub commands follow:

ALTER TABLE ADD COLUMN

The following statement will add a new column to the table:

1
2
ALTER TABLE employees
ADD COLUMN salary INTEGER;

Now execute the following command to confirm the details were successfully added to the employees table:

1
2
3
4
5
6
7
8
9
10
SHOW COLUMNS FROM employees;
  column_name |  data_type  | is_nullable | column_default | generation_expression |  indices  | is_hidden
--------------+-------------+-------------+----------------+-----------------------+-----------+------------
  id          | INT8        |    FALSE    | NULL           |                       | {PRIMARY} |   FALSE
  first_name  | VARCHAR(50) |    TRUE     | NULL           |                       | {}        |   FALSE
  last_name   | VARCHAR(50) |    TRUE     | NULL           |                       | {}        |   FALSE
  gender      | VARCHAR(10) |    TRUE     | NULL           |                       | {}        |   FALSE
  email       | VARCHAR(50) |    TRUE     | NULL           |                       | {}        |   FALSE
  salary      | INT8        |    TRUE     | NULL           |                       | {}        |   FALSE
(6 ROWS)

Note that the new column salary has been successfully added to the table.

ALTER TABLE ADD CONSTRAINT

Now add a constraint to the columns of the table with the following command:

1
2
3
ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE ( email ),
ADD CONSTRAINT check_salary CHECK ( salary > 0 );

As shown below, viewing the table should confirm the constraint have been successfully added:

1
2
3
4
5
6
7
SHOW CONSTRAINT FROM employees;
  TABLE_NAME | constraint_name | constraint_type |       details        | validated
-------------+-----------------+-----------------+----------------------+------------
  employees  | check_salary    | CHECK           | CHECK ((salary > 0)) |   TRUE
  employees  | PRIMARY         | PRIMARY KEY     | PRIMARY KEY (id ASC) |   TRUE
  employees  | unique_email    | UNIQUE          | UNIQUE (email ASC)   |   TRUE
(3 ROWS)

ALTER TABLE ALTER COLUMN

The following example demonstrates how to alter the column first_name and set a NOT NULL constraint:

1
2
ALTER TABLE employees
ALTER COLUMN first_name SET NOT NULL;

Now execute the following command to again confirm the changes were successfully executed:

1
2
3
4
5
6
7
8
9
10
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
  gender      | VARCHAR(10) |    TRUE     | NULL           |                       | {}                     |   FALSE
  email       | VARCHAR(50) |    TRUE     | NULL           |                       | {unique_email}         |   FALSE
  salary      | INT8        |    TRUE     | NULL           |                       | {}                     |   FALSE
(6 ROWS)

As noted above, the column name has been changed to “not nullable.”

ALTER TABLE ALTER TYPE

This next example will explain how to change the data type of the email column from VARCHAR to STRING:

1
2
ALTER TABLE employees
ALTER email TYPE STRING;

Viewing the table again should show the column has successfully been changed:

1
2
3
4
5
6
7
8
9
10
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
  gender      | VARCHAR(10) |    TRUE     | NULL           |                       | {}                     |   FALSE
  email       | STRING      |    TRUE     | NULL           |                       | {unique_email}         |   FALSE
  salary      | INT8        |    TRUE     | NULL           |                       | {}                     |   FALSE
(6 ROWS)

Conclusion

This was part one in a tutorial series explaining how to use the CockroachDB alter table statement, along with the commonly used sub commands, to make alterations to an existing table. This tutorial first covered how to create a table, add a new column and inset records into the new table. The part one then explained the list and uses of the CockroachDB alter table statement sub commands and provided working examples, including how to add a constraint to the columns of the table and how to change the column’s data type. Remember to always view the table after executing any modifications to confirm the changes were successful.

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.