The CockroachDB Alter Table Part 2
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