This page provides an overview on changing and removing the objects in a database schema, with some simple examples based on Cockroach Labs' fictional vehicle-sharing company, MovR.
Before you begin
Before reading this page, do the following:
- Create a CockroachDB Serverless cluster or start a local cluster.
- Review the database schema objects.
- Create a database.
- Create a user-defined schema.
- Create a table.
- Add secondary indexes.
Alter database schema objects
To change an existing object in a database schema, use an ALTER
statement.
ALTER
statements generally take the following form:
ALTER {OBJECT_TYPE} {object_name} {SUBCOMMAND};
Parameter | Description |
---|---|
{OBJECT_TYPE} |
The type of the object. |
{object_name} |
The name of the object. |
{SUBCOMMAND} |
The subcommand for the change that you would like to make. |
For examples, see below.
CockroachDB supports the following ALTER
statements:
ALTER DATABASE
ALTER SCHEMA
ALTER TABLE
ALTER INDEX
ALTER VIEW
ALTER SEQUENCE
ALTER TYPE
ALTER USER/ROLE
Best practices for altering objects
After you initialize a database schema, make any additional database schema changes in a separate set of changes (e.g., for the Cockroach SQL client, a separate
.sql
file; for Liquibase, a separate changeset).For
ALTER TABLE
statements, combine multiple subcommands in a singleALTER TABLE
statement, where possible.As a general best practice, we discourage the use of client libraries to execute database schema changes. Instead, use a database schema migration tool, or the CockroachDB SQL client.
Review the limitations of online schema changes in CockroachDB. Note that CockroachDB has limited support for schema changes within the same explicit transaction.
We recommend doing schema changes outside explicit transactions, where possible. When a database schema management tool manages transactions on your behalf, we recommend only including one schema change operation per transaction.
Altering objects examples
Suppose you want to make some changes to the users
table that you created in Create a Table. In specific, you want to do the following:
- Add a new
username
column. - Change the columns in the table's primary key to
username
column andemail
. - Move the table to the
abbey_schema
user-defined schema. - Change the owner of the table to
abbey
.
The ALTER TABLE
statement has subcommands for all of these changes:
- To add a new column, use the
ADD COLUMN
subcommand. - To change the primary key columns of a table, use the
ALTER PRIMARY KEY
subcommand. - To move a table to a different schema, use the
SET SCHEMA
subcommand. - To change the owner of a table, use the
OWNER TO
subcommand.
Create a new .sql
file for the changes that you plan to make to the table:
$ touch update_users_table.sql
Open update_users_table.sql
in a text editor, and add the ALTER TABLE
statement for adding the username
column:
ALTER TABLE IF EXISTS movr.max_schema.users ADD COLUMN username STRING;
Under that first ALTER TABLE
statement, add another ALTER TABLE
statement for changing the primary key columns to username
and email
:
ALTER TABLE IF EXISTS movr.max_schema.users ALTER PRIMARY KEY USING COLUMNS (username, email);
In order to add a column to an existing table's primary key index, the column must have an existing NOT NULL
constraint. Neither the username
nor the email
columns have NOT NULL
constraints.
Add a NOT NULL
constraint to the ADD COLUMN
subcommand for username
. In the same ALTER TABLE
statement, add an ALTER COLUMN
subcommand to set the NOT NULL
constraint on the email
column:
ALTER TABLE IF EXISTS movr.max_schema.users
ADD COLUMN username STRING NOT NULL,
ALTER COLUMN email SET NOT NULL;
The file should now look something like this:
ALTER TABLE IF EXISTS movr.max_schema.users
ADD COLUMN username STRING NOT NULL,
ALTER COLUMN email SET NOT NULL;
ALTER TABLE IF EXISTS movr.max_schema.users ALTER PRIMARY KEY USING COLUMNS (username, email);
The remaining changes that you want to make will require ALTER TABLE
statements with the SET SCHEMA
and OWNER TO
subcommands. An ALTER TABLE ... SET SCHEMA
statement will change the contents of two schemas, and an ALTER TABLE ... OWNER TO
statement will change the privileges of two users. To follow authorization best practices, you should execute any statements that change databases, user-defined schemas, or user privileges as a member of the admin
role (e.g., as root
).
Create a new .sql
file for the remaining ALTER TABLE
statements, to be executed by root
:
$ touch update_users_owner.sql
Add the following statements to the file:
ALTER TABLE IF EXISTS movr.max_schema.users SET SCHEMA abbey_schema;
ALTER TABLE IF EXISTS movr.abbey_schema.users OWNER TO abbey;
To execute the statements in the update_users_table.sql
file as max
, run the following command:
$ cockroach sql \
--certs-dir={certs-directory} \
--user=max \
--database=movr \
-f update_users_table.sql
To execute the statements in the update_users_owner.sql
file as root
, run the following command:
$ cockroach sql \
--certs-dir={certs-directory} \
--user=root \
--database=movr \
-f update_users_owner.sql
The users
table should now have a new column, a different primary key, a different schema, and a different owner.
You can verify with some SHOW
statements:
$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
--execute="SHOW SCHEMAS; SHOW TABLES; SHOW CREATE TABLE movr.abbey_schema.users;"
schema_name | owner
---------------------+--------
abbey_schema | abbey
crdb_internal | NULL
information_schema | NULL
max_schema | max
pg_catalog | NULL
pg_extension | NULL
public | admin
(7 rows)
schema_name | table_name | type | owner | estimated_row_count
---------------+------------------+-------+-------+----------------------
abbey_schema | user_promo_codes | table | abbey | 0
abbey_schema | users | table | abbey | 0
max_schema | rides | table | max | 0
max_schema | vehicles | table | max | 0
(4 rows)
table_name | create_statement
--------------------------+-----------------------------------------------------------------------------------
movr.abbey_schema.users | CREATE TABLE abbey_schema.users (
| first_name STRING NOT NULL,
| last_name STRING NOT NULL,
| email STRING NOT NULL,
| username STRING NOT NULL,
| CONSTRAINT "primary" PRIMARY KEY (username ASC, email ASC),
| UNIQUE INDEX users_first_name_last_name_key (first_name ASC, last_name ASC),
| UNIQUE INDEX users_email_key (email ASC),
| FAMILY "primary" (first_name, last_name, email, username)
| )
(1 row)
Drop database schema objects
To drop an object from a database schema, use a DROP
statement.
DROP
statements generally take the following form:
DROP {OBJECT_TYPE} {object_name} CASCADE;
Parameter | Description |
---|---|
{OBJECT_TYPE} |
The type of the object. |
{object_name} |
The name of the object. |
{CASCADE} |
An optional keyword that will drop all objects dependent on the object being dropped. |
For examples, see below.
CockroachDB supports the following DROP
statements:
To drop columns and column constraints from a table, use the DROP COLUMN
and DROP CONSTRAINT
subcommands of the ALTER TABLE
statement.
Drop best practices
- Check the contents and dependencies of the object that you want to drop before using the
CASCADE
option.CASCADE
drops all of the contents of an object, and should be used sparingly after a schema has been initialized.
Drop example
Suppose that you want to drop an index that isn't being used very much. In particular, you want to drop the index on first_name
and last_name
from the users
table.
$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
--execute="SHOW INDEXES FROM movr.abbey_schema.users; SHOW CREATE TABLE movr.abbey_schema.users;"
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+--------------------------------+------------+--------------+-------------+-----------+---------+-----------
users | primary | false | 1 | username | ASC | false | false
users | primary | false | 2 | email | ASC | false | false
users | users_first_name_last_name_key | false | 1 | first_name | ASC | false | false
users | users_first_name_last_name_key | false | 2 | last_name | ASC | false | false
users | users_first_name_last_name_key | false | 3 | username | ASC | false | true
users | users_first_name_last_name_key | false | 4 | email | ASC | false | true
users | users_email_key | false | 1 | email | ASC | false | false
users | users_email_key | false | 2 | username | ASC | false | true
(8 rows)
table_name | create_statement
--------------------------+-----------------------------------------------------------------------------------
movr.abbey_schema.users | CREATE TABLE abbey_schema.users (
| first_name STRING NOT NULL,
| last_name STRING NOT NULL,
| email STRING NOT NULL,
| username STRING NOT NULL,
| CONSTRAINT "primary" PRIMARY KEY (username ASC, email ASC),
| UNIQUE INDEX users_first_name_last_name_key (first_name ASC, last_name ASC),
| UNIQUE INDEX users_email_key (email ASC),
| FAMILY "primary" (first_name, last_name, email, username)
| )
(1 row)
Note that users_first_name_last_name_key
is a UNIQUE
index, which means that it implies a dependent, UNIQUE
constraint. To drop indexes with dependencies, you can use the CASCADE
keyword.
Create a new file, and add the DROP
statement:
$ touch drop_unique_users_idx.sql
After creation, the notation for referring to indexes in CockroachDB is [table_name]@[index_name]
.
DROP INDEX movr.abbey_schema.users@users_first_name_last_name_key CASCADE;
To drop the index, execute the file:
$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
-f drop_unique_users_idx.sql
$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
--execute="SHOW INDEXES FROM movr.abbey_schema.users;"
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+-----------------+------------+--------------+-------------+-----------+---------+-----------
users | primary | false | 1 | username | ASC | false | false
users | primary | false | 2 | email | ASC | false | false
users | users_email_key | false | 1 | email | ASC | false | false
users | users_email_key | false | 2 | username | ASC | false | true
(4 rows)
What's next?
- Read about Online Schema Changes in CockroachDB.
- Write data
- Read data
You might also be interested in the following pages: