The CockroachDB Alter Table Part 1
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:
| SUBCOMMAND | DESCRIPTION |
|---|---|
| ADD COLUMN | used to add a column |
| ADD CONSTRAINT | used to add a constraint on a table |
| ALTER COLUMN | used to change a column or drop the column’s constraint |
| ALTER TYPE | used to change the columns data type |
| CONFIGURE ZONE | used to configure replication zones |
| DROP COLUMN | used to delete a column on a table |
| DROP CONSTRAINT | used to delete the constraint on the table |
| EXPERIMENTAL_AUDIT | used to enable or disable the audit logs |
| PARTITION BY | used to partition, re-partition or un-partition a table |
| RENAME COLUMN | used to change the name of a column |
| RENAME CONSTRAINT | used to change the column constraint |
| RENAME TABLE | used to change the name of a table |
| SPLIT AT | used to make a force range split at the row of the table |
| UNSPLIT AT | used to delete the force range split |
| VALIDATE CONSTRAINT | check 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