Change and Remove Objects in a Database Schema

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:

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:

Best practices for altering objects

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 and email.
  • 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:

Create a new .sql file for the changes that you plan to make to the table:

icon/buttons/copy
$ 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:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
$ touch update_users_owner.sql

Add the following statements to the file:

icon/buttons/copy
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:

icon/buttons/copy
$ 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:

icon/buttons/copy
$ 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:

icon/buttons/copy
$ 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:

Note:

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.

icon/buttons/copy
$ 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:

icon/buttons/copy
$ touch drop_unique_users_idx.sql
Note:

After creation, the notation for referring to indexes in CockroachDB is [table_name]@[index_name].

icon/buttons/copy
DROP INDEX movr.abbey_schema.users@users_first_name_last_name_key CASCADE;

To drop the index, execute the file:

icon/buttons/copy
$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
-f drop_unique_users_idx.sql
icon/buttons/copy
$ 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?

You might also be interested in the following pages:

YesYes NoNo