> ## Documentation Index
> Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Change and Remove Objects in a Database Schema

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

This page provides an overview on changing and removing the objects in a database schema, with some simple examples based on Cockroach Labs's fictional vehicle-sharing company, <InternalLink path="movr">MovR</InternalLink>.

## Before you begin

Before reading this page, do the following:

* <InternalLink version="cockroachcloud" path="quickstart">Create a CockroachDB Standard cluster</InternalLink> or <InternalLink path="start-a-local-cluster">start a local cluster</InternalLink>.
* <InternalLink path="schema-design-overview">Review the database schema objects</InternalLink>.
* <InternalLink path="schema-design-database">Create a database</InternalLink>.
* <InternalLink path="schema-design-schema">Create a user-defined schema</InternalLink>.
* <InternalLink path="schema-design-table">Create a table</InternalLink>.
* <InternalLink path="schema-design-indexes">Add secondary indexes</InternalLink>.

## Alter database schema objects

To change an existing object in a database schema, use an `ALTER` statement.

`ALTER` statements generally take the following form:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER {OBJECT_TYPE} {object_name} {SUBCOMMAND};
```

| Parameter                     | Description                                                |
| ----------------------------- | ---------------------------------------------------------- |
| `{OBJECT\_TYPE}`              | The type of the object.                                    |
| `{object\_name}`              | The name of the object.                                    |
| <code>{'{SUBCOMMAND}'}</code> | The subcommand for the change that you would like to make. |

For examples, see [below](#altering-objects-examples).

CockroachDB supports the following `ALTER` statements:

* <InternalLink path="alter-database">`ALTER DATABASE`</InternalLink>
* <InternalLink path="alter-schema">`ALTER SCHEMA`</InternalLink>
* <InternalLink path="alter-table">`ALTER TABLE`</InternalLink>
* <InternalLink path="alter-index">`ALTER INDEX`</InternalLink>
* <InternalLink path="alter-view">`ALTER VIEW`</InternalLink>
* <InternalLink path="alter-sequence">`ALTER SEQUENCE`</InternalLink>
* <InternalLink path="alter-type">`ALTER TYPE`</InternalLink>
* <InternalLink path="alter-user">`ALTER USER/ROLE`</InternalLink>

### 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 single `ALTER TABLE` statement, where possible.
* Use a <InternalLink path="third-party-database-tools#schema-migration-tools">database schema migration tool</InternalLink> or the <InternalLink path="cockroach-sql">CockroachDB SQL client</InternalLink> instead of a <InternalLink path="third-party-database-tools#drivers">client library</InternalLink> to execute <InternalLink path="online-schema-changes">database schema changes</InternalLink>.
* Review the <InternalLink path="online-schema-changes#limitations">limitations of online schema changes</InternalLink>. CockroachDB <InternalLink path="online-schema-changes#schema-changes-within-transactions">doesn't guarantee the atomicity of schema changes within transactions with multiple statements</InternalLink>.

  Cockroach Labs recommends that you perform schema changes outside explicit transactions. When a database <InternalLink path="third-party-database-tools#schema-migration-tools">schema management tool</InternalLink> manages transactions on your behalf, include one schema change operation per transaction.

### Altering objects examples

Suppose you want to make some changes to the `users` table that you created in <InternalLink path="schema-design-table">Create a Table</InternalLink>. 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:

* To add a new column, use the <InternalLink path="alter-table#add-column">`ADD COLUMN` subcommand</InternalLink>.
* To change the primary key columns of a table, use the <InternalLink path="alter-table#alter-primary-key">`ALTER PRIMARY KEY` subcommand</InternalLink>.
* To move a table to a different schema, use the <InternalLink path="alter-table#set-schema">`SET SCHEMA`</InternalLink> subcommand.
* To change the owner of a table, use the <InternalLink path="alter-table#owner-to">`OWNER TO`</InternalLink> subcommand.

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

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="not-null">`NOT NULL` constraint</InternalLink>. 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 <InternalLink path="alter-table#alter-column">`ALTER COLUMN` subcommand</InternalLink> to set the `NOT NULL` constraint on the `email` column:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="security-reference/authorization#authorization-best-practices">authorization best practices</InternalLink>, 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`:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ touch update_users_owner.sql
```

Add the following statements to the file:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ 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:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ 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:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
--execute="SHOW SCHEMAS; SHOW TABLES; SHOW CREATE TABLE movr.abbey_schema.users;"
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     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:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DROP {OBJECT_TYPE} {object_name} CASCADE;
```

| Parameter                  | Description                                                                           |
| -------------------------- | ------------------------------------------------------------------------------------- |
| `{OBJECT\_TYPE}`           | The type of the object.                                                               |
| `{object\_name}`           | The name of the object.                                                               |
| <code>{'{CASCADE}'}</code> | An optional keyword that will drop all objects dependent on the object being dropped. |

For examples, see [below](#altering-objects-examples).

CockroachDB supports the following `DROP` statements:

* <InternalLink path="drop-database">`DROP DATABASE`</InternalLink>
* <InternalLink path="drop-schema">`DROP SCHEMA`</InternalLink>
* <InternalLink path="drop-table">`DROP TABLE`</InternalLink>
* <InternalLink path="drop-index">`DROP INDEX`</InternalLink>
* <InternalLink path="drop-sequence">`DROP SEQUENCE`</InternalLink>
* <InternalLink path="drop-view">`DROP VIEW`</InternalLink>
* <InternalLink path="drop-user">`DROP USER/ROLE`</InternalLink>

<Note>
  To drop columns and column constraints from a table, use the `DROP COLUMN` and `DROP CONSTRAINT` subcommands of the <InternalLink path="alter-table">`ALTER TABLE`</InternalLink> statement.
</Note>

### 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.

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ 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;"
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  table_name |           index_name           | non_unique | seq_in_index | column_name | direction | storing | implicit  | visible
-------------+--------------------------------+------------+--------------+-------------+-----------+---------+-----------+---------
  users      | users_pkey                     |    f       |            1 | username    | ASC       |   f     |    f      |   t
  users      | users_pkey                     |    f       |            2 | email       | ASC       |   f     |    f      |   t
  users      | users_first_name_last_name_key |    f       |            1 | first_name  | ASC       |   f     |    f      |   t
  users      | users_first_name_last_name_key |    f       |            2 | last_name   | ASC       |   f     |    f      |   t
  users      | users_first_name_last_name_key |    f       |            3 | username    | ASC       |   f     |    t      |   t
  users      | users_first_name_last_name_key |    f       |            4 | email       | ASC       |   f     |    t      |   t
  users      | users_email_key                |    f       |            1 | email       | ASC       |   f     |    f      |   t
  users      | users_email_key                |    f       |            2 | username    | ASC       |   f     |    t      |   t
(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 <InternalLink path="drop-index#remove-an-index-and-dependent-objects-with-cascade">drop indexes with dependencies</InternalLink>, you can use the `CASCADE` keyword.

Create a new file, and add the `DROP` statement:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ touch drop_unique_users_idx.sql
```

<Note>
  After creation, the notation for referring to indexes in CockroachDB is `[table_name]@[index_name]`.
</Note>

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DROP INDEX movr.abbey_schema.users@users_first_name_last_name_key CASCADE;
```

To drop the index, execute the file:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
-f drop_unique_users_idx.sql
```

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
--execute="SHOW INDEXES FROM movr.abbey_schema.users;"
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  table_name |   index_name    | non_unique | seq_in_index | column_name | direction | storing | implicit  | visible
-------------+-----------------+------------+--------------+-------------+-----------+---------+-----------+---------
  users      | users_pkey      |    f       |            1 | username    | ASC       |   f     |   f       |   t
  users      | users_pkey      |    f       |            2 | email       | ASC       |   f     |   f       |   t
  users      | users_email_key |    f       |            1 | email       | ASC       |   f     |   f       |   t
  users      | users_email_key |    f       |            2 | username    | ASC       |   f     |   t       |   t
(4 rows)
```

## What's next?

* <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>
* <InternalLink path="insert-data">Write Data</InternalLink>
* <InternalLink path="query-data">Read Data</InternalLink>

You might also be interested in the following pages:

* <InternalLink path="cockroach-commands">`cockroach` Commands Overview</InternalLink>