> ## Documentation Index > Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # ALTER TABLE 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 {children} ; }; export const version = "v23.2"; The `ALTER TABLE` statement changes the definition of a table. For information on using `ALTER TABLE`, see the pages for its [subcommands](#subcommands). This schema change statement is registered as a job. You can view long-running jobs with `SHOW JOBS`. ## Required privileges Refer to the respective [subcommands](#subcommands). ## Synopsis alter_table syntax diagram alter_table_cmds syntax diagram where `alter_table_cmd` is: ## Parameters | Parameter | Description | | ------------- | ------------------------------------------------------------------------------------------------------------- | | `IF EXISTS` | Change the table only if a table with the current name exists; if one does not exist, do not return an error. | | `table\_name` | The name of the table you want to change. | Additional parameters are documented for the respective [subcommands](#subcommands). ## Subcommands Some subcommands can be used in combination in a single `ALTER TABLE` statement. For example, you can [atomically rename a column and add a new column with the old name of the existing column](#add-and-rename-columns-atomically). | Subcommand | Description | Can combine with other subcommands? | | ------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------- | | [`ADD COLUMN`](#add-column) | Add columns to tables. | Yes | | [`ADD CONSTRAINT`](#add-constraint) | Add constraints to columns. | Yes | | [`ALTER COLUMN`](#alter-column) | Change an existing column. | Yes | | [`ALTER PRIMARY KEY`](#alter-primary-key) | Change the primary key of a table. | Yes | | [`CONFIGURE ZONE`](#configure-zone) | Replication Controls for a table. | No | | [`DROP COLUMN`](#drop-column) | Remove columns from tables. | Yes | | [`DROP CONSTRAINT`](#drop-constraint) | Remove constraints from columns. | Yes | | [`EXPERIMENTAL\_AUDIT`](#experimental_audit) | Enable per-table audit logs, for security purposes. | Yes | | [`OWNER TO`](#owner-to) | Change the owner of the table. | No | | [`PARTITION BY`](#partition-by) | Partition, re-partition, or un-partition a table. | Yes | | [`RENAME COLUMN`](#rename-column) | Change the names of columns. | Yes | | [`RENAME CONSTRAINT`](#rename-constraint) | Change constraints columns. | Yes | | [`RENAME TO`](#rename-to) | Change the names of tables. | No | | [`RESET {storage parameter}`](#reset-storage-parameter) | Reset a storage parameter on a table to its default value. | Yes | | [`SET {storage parameter}`](#set-storage-parameter) | Set a storage parameter on a table. | Yes | | [`SET LOCALITY`](#set-locality) | Set the table locality for a table in a multi-region database. | No | | [`SET SCHEMA`](#set-schema) | Change the schema of a table. | No | | [`SCATTER`](#scatter) | Makes a best-effort attempt to redistribute replicas and leaseholders for the ranges of a table or index. Note that it does not return an error even if replicas are not moved. | No | | [`SPLIT AT`](#split-at) | Force a range split at the specified row in the table. | No | | [`UNSPLIT AT`](#unsplit-at) | Remove a range split enforcement in the table. | No | | [`VALIDATE CONSTRAINT`](#validate-constraint) | Check whether values in a column match a constraint on the column. | Yes | ### `ADD COLUMN` Use `ALTER TABLE... ADD COLUMN` to add columns to existing tables. For examples, see [Add columns](#add-columns). #### Required privileges The user must have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `IF NOT EXISTS` | Add a column only if a column of the same name does not already exist; if one does exist, do not return an error. | | `column\_name` | The name of the column you want to add. The column name must follow these identifier rules and must be unique within the table but can have the same name as indexes or constraints. | | `typename` | The data type of the new column. | | `col\_qualification` | An optional list of column qualifications. | For usage, see [Synopsis](#synopsis). ### `ADD CONSTRAINT` Use `ALTER TABLE... ADD CONSTRAINT` to add the following constraints to columns: * [`UNIQUE`](#add-the-unique-constraint) * [`CHECK`](#add-the-check-constraint) * [`FOREIGN KEY`](#add-the-foreign-key-constraint-with-cascade) To add a primary key constraint to a table, you should explicitly define the primary key at table creation. To replace an existing primary key, you can use `ADD CONSTRAINT... PRIMARY KEY`. For details, see [Changing primary keys with `ADD CONSTRAINT... PRIMARY KEY`](#changing-primary-keys-with-add-constraint-primary-key). The `DEFAULT` and `NOT NULL` constraints are managed through `ALTER COLUMN`. For examples, see [Add constraints](#add-constraints). #### Required privileges The user must have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | ------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `IF NOT EXISTS` | Add a constraint only if a constraint of the same name does not already exist; if one does exist, do not return an error. | | `constraint\_name` | The name of the constraint, which must be unique to its table and follow these identifier rules. | | `constraint\_elem` | The `CHECK`, foreign key, or `UNIQUE` constraint you want to add. Adding/changing a `DEFAULT` constraint is done through [`ALTER COLUMN`](#alter-column). Adding/changing the table's `PRIMARY KEY` is not supported through `ALTER TABLE`; it can only be specified during table creation. | | `NOT VALID` | Create unvalidated constraints. When creating an unvalidated constraint, the system does not check that existing table data satisfies the constraint. The constraint is still enforced when table data is modified. An unvalidated constraint can later be validated using [`VALIDATE CONSTRAINT`](#validate-constraint). | For usage, see [Synopsis](#synopsis). #### Changing primary keys with `ADD CONSTRAINT... PRIMARY KEY` When you change a primary key with [`ALTER TABLE... ALTER PRIMARY KEY`](#alter-primary-key), the existing primary key index becomes a secondary index. The secondary index created by `ALTER PRIMARY KEY` takes up node memory and can slow down write performance to a cluster. If you do not have queries that filter on the primary key that you are replacing, you can use `ADD CONSTRAINT` to replace the existing primary index without creating a secondary index. You can use `ADD CONSTRAINT... PRIMARY KEY` to add a primary key to an existing table if one of the following is true: * No primary key was explicitly defined at table creation. In this case, the table is created with a default primary key on `rowid`. Using `ADD CONSTRAINT... PRIMARY KEY` drops the default primary key and replaces it with a new primary key. * A `DROP CONSTRAINT` statement precedes the `ADD CONSTRAINT... PRIMARY KEY` statement, in the same transaction. For an example, see [Drop and add the primary key constraint](#drop-and-add-a-primary-key-constraint). #### Aliases In CockroachDB, the following are aliases for `ALTER TABLE... ADD CONSTRAINT... PRIMARY KEY`: * `ALTER TABLE... ADD PRIMARY KEY` ### `ALTER COLUMN` Use `ALTER TABLE... ALTER COLUMN` to do the following: * Set, change, or drop a column's `DEFAULT` constraint. * Set or drop a column's `NOT NULL` constraint. * Set, change, or drop an `ON UPDATE` expression. * Change a column's data type. * Set the [visibility](#set-the-visibility-of-a-column) of a column. Support for altering column data types is in preview, with certain limitations. For details, see [Altering column data types](#alter-column-data-types). For examples, see [Alter columns](#alter-columns). #### Required privileges The user must have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `column\_name` | The name of the column to modify. | | `a\_expr` | The new default value to set. | | `b\_expr` | The `ON UPDATE` expression to set. | | `[NOT] VISIBLE` | The visibility of a column when using `\*` in a `SELECT` clause. | | `typename` | The new data type you want to use. Support for altering column types is in preview, with certain limitations. For details, see [Alter column data types](#alter-column-data-types). | | `USING a\_expr` | How to compute a new column value from the old column value. | For usage, see [Synopsis](#synopsis). #### Alter column data types Support for altering column data types is in preview, with certain limitations. To enable column type altering, set the `enable_experimental_alter_column_type_general` session variable to `true`. The following are equivalent in CockroachDB: * `ALTER TABLE... ALTER... TYPE` * `ALTER TABLE... ALTER COLUMN TYPE` * `ALTER TABLE... ALTER COLUMN SET DATA TYPE` #### Limitations on altering data types You cannot alter the data type of a column if: * The column is part of an index. * The column has `CHECK` constraints. * The column owns a sequence. * The column has a `DEFAULT` expression. This will result in an `ERROR:... column... cannot also have a DEFAULT expression` with `SQLSTATE: 42P16`. * The `ALTER COLUMN TYPE` statement is part of a combined `ALTER TABLE` statement. * The `ALTER COLUMN TYPE` statement is inside an explicit transaction. Most `ALTER COLUMN TYPE` changes are finalized asynchronously. Schema changes on the table with the altered column may be restricted, and writes to the altered column may be rejected until the schema change is finalized. ### `ALTER PRIMARY KEY` Use `ALTER TABLE... ALTER PRIMARY KEY` to change the primary key of a table. Note the following: * You cannot change the primary key of a table that is currently undergoing a primary key change, or any other schema change. * `ALTER PRIMARY KEY` might need to rewrite multiple indexes, which can make it an expensive operation. * When you change a primary key with `ALTER PRIMARY KEY`, the old primary key index becomes a `UNIQUE` secondary index. This helps optimize the performance of queries that still filter on the old primary key column. * `ALTER PRIMARY KEY` does not alter the partitions on a table or its indexes, even if a partition is defined on a column in the original primary key. If you alter the primary key of a partitioned table, you must update the table partition accordingly. * The secondary index created by `ALTER PRIMARY KEY` will not be partitioned, even if a partition is defined on a column in the original primary key. To ensure that the table is partitioned correctly, you must create a partition on the secondary index, or drop the secondary index. * Any new primary key column set by `ALTER PRIMARY KEY` must have an existing `NOT NULL` constraint. To add a `NOT NULL` constraint to an existing column, use [`ALTER TABLE... ALTER COLUMN... SET NOT NULL`](#set-not-null-constraint). To change an existing primary key without creating a secondary index from that primary key, use [`DROP CONSTRAINT... PRIMARY KEY`/`ADD CONSTRAINT... PRIMARY KEY`](#changing-primary-keys-with-add-constraint-primary-key). For examples, see [Add constraints](#add-constraints) and [Drop constraints](#drop-constraints). For examples, see [Alter a primary key](#alter-a-primary-key). #### Required privileges The user must have the `CREATE` privilege on a table to alter its primary key. #### Parameters | Parameter | Description | | --------------- | ---------------------------------------------------------------------------------------------------------------------------- | | `index\_params` | The name of the column(s) that you want to use for the primary key. These columns replace the current primary key column(s). | | `USING HASH` | Creates a hash-sharded index. | For usage, see [Synopsis](#synopsis). ### `CONFIGURE ZONE` `ALTER TABLE... CONFIGURE ZONE` is used to add, modify, reset, or remove replication zones for a table. To view details about existing replication zones, use `SHOW ZONE CONFIGURATIONS`. For more information about replication zones, see Replication Controls. You can use *replication zones* to control the number and location of replicas for specific sets of data, both when replicas are first added and when they are rebalanced to maintain cluster equilibrium. For examples, see [Replication Controls](#configure-replication-zones). For instructions showing how to troubleshoot replication zones that may be misconfigured, see Troubleshoot Replication Zone Configurations. #### Required privileges The user must be a member of the `admin` role or have been granted `CREATE` or `ZONECONFIG` privileges. To configure `system` objects, the user must be a member of the `admin` role. #### Parameters | Parameter | Description | | ---------- | ------------------------------------------------------------------------------------------------------------------------------------------------ | | `variable` | The name of the replication zone variable to change. | | `value` | The value of the replication zone variable to change. | | `DISCARD` | Remove a replication zone. | For usage, see [Synopsis](#synopsis). ### `DROP COLUMN` Use `ALTER TABLE... DROP COLUMN` to remove columns from a table. When used in an explicit transaction combined with other schema changes to the same table, `DROP COLUMN` can result in data loss if one of the other schema changes fails or is canceled. To work around this, move the `DROP COLUMN` statement to its own explicit transaction or run it in a single statement outside the existing transaction. By default, `DROP COLUMN` drops any indexes on the column being dropped, and any indexes that reference the column, including indexes with `STORING` clauses that reference the column. For examples, see [Drop columns](#drop-columns). #### Known limitations * CockroachDB prevents a column from being dropped if it is referenced by a partial index predicate. To drop such a column, the partial indexes need to be dropped first using `DROP INDEX`. #### Required privileges The user must have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `column\_name` | The name of the column you want to drop. When a column with a `CHECK` constraint is dropped, the `CHECK` constraint is also dropped. | | `CASCADE` | Drop the column even if objects (such as views) depend on it; drop the dependent objects, as well. `CASCADE` will drop a column with a foreign key constraint if it is the only column in the reference. `CASCADE` does not list the objects it drops, so should be used cautiously. `CASCADE` is not required to drop an indexed column, or a column that is referenced by an index. By default, `DROP COLUMN` drops any indexes on the column being dropped, and any indexes that reference the column, including partial indexes with predicates that reference the column and indexes with `STORING` clauses that reference the column. | | `RESTRICT` | *(Default)* Do not drop the column if any objects (such as views) depend on it. | For usage, see [Synopsis](#synopsis). ### `DROP CONSTRAINT` Use `ALTER TABLE... DROP CONSTRAINT` to remove `CHECK` and `FOREIGN KEY` constraints from columns. `PRIMARY KEY` constraints can be dropped with `DROP CONSTRAINT` if an `ADD CONSTRAINT` statement follows the `DROP CONSTRAINT` statement in the same transaction. When you change a primary key with `ALTER TABLE... ALTER PRIMARY KEY`, the old primary key index becomes a secondary index. If you do not want the old primary key to become a secondary index, use `DROP CONSTRAINT`/`ADD CONSTRAINT` to change the primary key. For information about removing other constraints, see Constraints: Remove Constraints. For examples, see [Drop constraints](#drop-constraints). #### Required privileges The user must have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | ------------------ | -------------------------------------------- | | `constraint\_name` | The name of the constraint you want to drop. | For usage, see [Synopsis](#synopsis). ### `EXPERIMENTAL_AUDIT` `ALTER TABLE... EXPERIMENTAL_AUDIT` enables or disables the recording of SQL audit events to the `SENSITIVE_ACCESS` logging channel for a table. The `SENSITIVE_ACCESS` log output is also called the *SQL audit log*. For details on using SQL audit logs, see SQL Audit Logging. **This feature is in preview** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com). SQL audit logs contain detailed information about queries being executed against your system, including: * Full text of the query (which may include personally identifiable information (PII)) * Date/Time * Client address * Application name CockroachDB stores audit log information in a way that ensures durability, but negatively impacts performance. As a result, we recommend using SQL audit logs for security purposes only. For more information, see Performance considerations. For examples, see [Configure audit logging](#configure-audit-logging). #### Required privileges Only members of the `admin` role can enable audit logs on a table. By default, the `root` user belongs to the `admin` role. #### Parameters | Parameter | Description | | --------- | ------------------------------------------- | | `READ` | Log all table reads to the audit log file. | | `WRITE` | Log all table writes to the audit log file. | | `OFF` | Turn off audit logging. | For usage, see [Synopsis](#synopsis). This command logs all reads and writes, and both the `READ` and `WRITE` parameters are required (as shown in the [examples](#configure-audit-logging)). ### `OWNER TO` `ALTER TABLE... OWNER TO` is used to change the owner of a table. For examples, see [Change table owner](#change-table-owner). #### Required privileges To change the owner of a table, the user must be an `admin` user, or the current owner of the table and a member of the new owner role. The new owner role must also have the `CREATE` privilege on the schema to which the table belongs. #### Parameters | Parameter | Description | | ------------ | ------------------------------------------ | | `role\_spec` | The role to set as the owner of the table. | For usage, see [Synopsis](#synopsis). ### `PARTITION BY` #### Required privileges `ALTER TABLE... PARTITION BY` is used to partition, re-partition, or un-partition a table. After defining partitions, [`CONFIGURE ZONE`](#configure-zone) is used to control the replication and placement of partitions. For examples, see [Define partitions](#define-partitions). #### Parameters | Parameter | Description | | ------------------- | ---------------------------------------------------------------------------------------------------- | | `name\_list` | List of columns you want to define partitions on (in the order they are defined in the primary key). | | `list\_partitions` | Name of list partition followed by the list of values to be included in the partition. | | `range\_partitions` | Name of range partition followed by the range of values to be included in the partition. | For usage, see [Synopsis](#synopsis). ### `RENAME COLUMN` `ALTER TABLE... RENAME COLUMN` changes the name of a column in a table. It is not possible to rename a column referenced by a view. For more details, see View Dependencies. For examples, see [Rename columns](#rename-columns). #### Required privileges The user must have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `IF EXISTS` | Rename the column only if a table of `table\_name` exists; if one does not exist, do not return an error. | | `column\_name` | The current name of the column. | | `column\_new\_name` | The `name` you want to use for the column, which must be unique to its table and follow these identifier rules. | For usage, see [Synopsis](#synopsis). ### `RENAME CONSTRAINT` `ALTER TABLE... RENAME CONSTRAINT` changes the name of a constraint on a column. It is not possible to rename a constraint for a column referenced by a view. For more details, see View Dependencies. For examples, see [Rename constraints](#rename-constraints). #### Required privileges The user must have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | ----------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `IF EXISTS` | Rename the constraint only if a constraint of `current\_name` exists; if one does not exist, do not return an error. | | `constraint\_name` | The current name of the constraint. | | `constraint\_new\_name` | The new `name` you want to use for the constraint, which must be unique to its table and follow these identifier rules. | For usage, see [Synopsis](#synopsis). ### `RENAME TO` `ALTER TABLE... RENAME TO` changes the name of a table. `ALTER TABLE... RENAME TO` cannot be used to move a table from one schema to another. To change a table's schema, use [`SET SCHEMA`](#set-schema). `ALTER TABLE... RENAME TO` cannot be used to move a table from one database to another. To change a table's database, use `BACKUP` and `RESTORE`. It is not possible to rename a table referenced by a view. For more details, see View Dependencies. For examples, see [Rename tables](#rename-tables). #### Required privileges The user must have the `DROP` privilege on the table and the `CREATE` on the parent database. #### Parameters | Parameter | Description | | ------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `table\_new\_name` | The new name of the table, which must be unique within its database and follow these identifier rules. When the parent database is not set as the default, the name must be formatted as `database.name`. The `UPSERT` and `INSERT ON CONFLICT` statements use a temporary table called `excluded` to handle uniqueness conflicts during execution. It's therefore not recommended to use the name `excluded` for any of your tables. | For usage, see [Synopsis](#synopsis). ### `RESET {storage parameter}` `ALTER TABLE... RESET {storage parameter}` reverts the value of a storage parameter on a table to its default value. To reset a storage parameter on an existing index, you must drop and recreate the index without the storage parameter. For examples, see [Set and reset storage parameters](#set-and-reset-storage-parameters). #### Required privileges The user must be a member of the `admin` or owner roles, or have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | ------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- | | `storage\_parameter\_key` | The name of the storage parameter you are changing. See [Table storage parameters](#table-storage-parameters) for a list of available parameters. | For usage, see [Synopsis](#synopsis). ### `SET {storage parameter}` `ALTER TABLE... SET {storage parameter}` sets a storage parameter on an existing table. To set a storage parameter on an existing index, you must drop and recreate the index with the storage parameter. For examples, see [Set and reset storage parameters](#set-and-reset-storage-parameters). #### Required privileges The user must be a member of the `admin` or owner roles, or have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | ------------------------- | -------------------------------------------------------------------------------------------------------------------------------- | | `storage\_parameter\_key` | The name of the storage parameter. See [Table storage parameters](#table-storage-parameters) for a list of available parameters. | | `value` | The value to assign the storage parameter. | For usage, see [Synopsis](#synopsis). #### Table storage parameters | Parameter name | Description | Data type | Default value | | ---------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- | ------------- | | `exclude\_data\_from\_backup` | Exclude the data in this table from any future backups. | Boolean | `false` | | New in v23.2.1:`schema\_locked` | Disallow schema changes on this table. Enabling `schema\_locked` can help improve performance of changefeeds running on this table. | Boolean | `false` | | `sql\_stats\_automatic\_collection\_enabled` | Enable automatic collection of full statistics for this table. | Boolean | `true` | | `sql\_stats\_automatic\_collection\_min\_stale\_rows` | Minimum number of stale rows in this table that will trigger a statistics refresh. | Integer | 500 | | `sql\_stats\_automatic\_collection\_fraction\_stale\_rows` | Fraction of stale rows in this table that will trigger a statistics refresh. | Float | 0.2 | | `sql\_stats\_forecasts\_enabled` | Enable forecasted statistics collection for this table. | Boolean | `true` | The following parameters are included for PostgreSQL compatibility and do not affect how CockroachDB runs: * `autovacuum_enabled` * `fillfactor` For the list of storage parameters that affect how Row-Level TTL works, see the list of TTL storage parameters. ### `SET LOCALITY` `ALTER TABLE.. SET LOCALITY` changes the table locality of a table in a multi-region database. While CockroachDB is processing an `ALTER TABLE.. SET LOCALITY` statement that enables or disables `REGIONAL BY ROW` on a table within a database, any `ADD REGION` and `DROP REGION` statements on that database will fail. For examples, see [Set localities](#set-localities). #### Required privileges The user must be a member of the `admin` or owner roles, or have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | ---------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `locality` | The `LOCALITY` clause, followed by the locality to apply to this table. Allowed values: | For usage, see [Synopsis](#synopsis). For more information about which table locality is right for your use case, see Table localities. ### `SET SCHEMA` `ALTER TABLE... SET SCHEMA` changes the schema of a table. CockroachDB supports `SET SCHEMA` as an alias for setting the `search_path` session variable. For examples, see [Set table schema](#set-table-schema). #### Required privileges The user must have the `DROP` privilege on the table, and the `CREATE` privilege on the schema. #### Parameters | Parameter | Description | | -------------- | ----------------------------------------- | | `schema\_name` | The name of the new schema for the table. | For usage, see [Synopsis](#synopsis). ### `SCATTER` `ALTER TABLE... SCATTER` runs a specified set of ranges for a table or index through the replication layer queue. If many ranges have been created recently, the replication queue may transfer some leases to other replicas to balance load across the cluster. Note that this statement makes a best-effort attempt to redistribute replicas and leaseholders for the ranges of an index. It does not return an error even if replicas are not moved. `SCATTER` has the potential to result in data movement proportional to the size of the table or index being scattered, thus taking additional time and resources to complete. For examples, see [Scatter tables](#scatter-tables). #### Required privileges The user must have the `INSERT` privilege on the table or index. #### Parameters | Parameter | Description | | -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------- | | `table\_name` | The name of the table that you want to scatter. | | `table\_index\_name` | The name of the index that you want to scatter. | | `expr\_list` | A list of scalar expressions in the form of the primary key of the table or the specified index. | For usage, see [Synopsis](#synopsis). ### `SPLIT AT` `ALTER TABLE... SPLIT AT` forces a range split at a specified row in the table. CockroachDB breaks data into ranges. By default, CockroachDB attempts to keep ranges below the default range size. To do this, the system will automatically split a range if it grows larger than this limit. For most use cases, this automatic range splitting is sufficient, and you should never need to worry about when or where the system decides to split ranges. However, there are reasons why you may want to perform manual splits on the ranges that store tables or indexes: * When a table only consists of a single range, all writes and reads to the table will be served by that range's leaseholder. If a table only holds a small amount of data but is serving a large amount of traffic, load distribution can become unbalanced and a hot spot can occur. Splitting the table's ranges manually can allow the load on the table to be more evenly distributed across multiple nodes. For tables consisting of more than a few ranges, load will naturally be distributed across multiple nodes and this will not be a concern. * When a table is created, it will only consist of a single range. If you know that a new table will immediately receive significant write traffic, you may want to preemptively split the table based on the expected distribution of writes before applying the load. This can help avoid reduced workload performance that results when automatic splits are unable to keep up with write traffic and a hot spot occurs. For examples, see [Split and unsplit tables](#split-and-unsplit-tables). #### Required privileges The user must have the `INSERT` privilege on the table. #### Parameters | Parameter | Description | | -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `select\_stmt` | A selection query that produces one or more rows at which to split the table. | | `a\_expr` | The expiration of the split enforcement on the table. This can be a `DECIMAL`, `INTERVAL`, `TIMESTAMP`, or `TIMESTAMPZ`. | For usage, see [Synopsis](#synopsis). ### `UNSPLIT AT` `ALTER TABLE... UNSPLIT AT` removes a [split enforcement](#split-at) on a range split, at a specified row in the table. Removing a split enforcement from a table or index ("unsplitting") allows CockroachDB to merge ranges as needed, to help improve your cluster's performance. For more information, see Range Merges. For examples, see [Split and unsplit tables](#split-and-unsplit-tables). #### Required privileges The user must have the `INSERT` privilege on the table. #### Parameters | Parameter | Description | | -------------- | ----------------------------------------------------------------------------------------------------------------------------------- | | `select\_stmt` | A selection query that produces one or more rows at which to unsplit a table. | | `ALL` | Remove all split enforcements for a table. | For usage, see [Synopsis](#synopsis). ### `VALIDATE CONSTRAINT` `ALTER TABLE... VALIDATE CONSTRAINT` checks whether values in a column match a constraint on the column. This statement is especially useful after applying a constraint to an existing column via [`ADD CONSTRAINT`](#add-constraint). In this case, `VALIDATE CONSTRAINT` can be used to find values already in the column that do not match the constraint. For examples, see [Validate constraints](#validate-constraints). #### Required privileges The user must have the `CREATE` privilege on the table. #### Parameters | Parameter | Description | | ------------------ | --------------------------------------- | | `constraint\_name` | The name of the constraint to validate. | For usage, see [Synopsis](#synopsis). ## View schema changes This schema change statement is registered as a job. You can view long-running jobs with `SHOW JOBS`. ## Examples ### Add columns The following examples use the `bank` demo database schema. To follow along, run `cockroach demo bank` to start a temporary, in-memory cluster with the `bank` schema and dataset preloaded: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo bank ``` #### Add a single column ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE bank ADD COLUMN active BOOL; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM bank; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------+-----------+-------------+----------------+-----------------------+-------------+------------ id | INT8 | false | NULL | | {bank_pkey} | false balance | INT8 | true | NULL | | {bank_pkey} | false payload | STRING | true | NULL | | {bank_pkey} | false active | BOOL | true | NULL | | {bank_pkey} | false (4 rows) ``` #### Add multiple columns ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE bank ADD COLUMN location STRING, ADD COLUMN currency STRING; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM bank; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------+-----------+-------------+----------------+-----------------------+-------------+------------ id | INT8 | false | NULL | | {bank_pkey} | false balance | INT8 | true | NULL | | {bank_pkey} | false payload | STRING | true | NULL | | {bank_pkey} | false active | BOOL | true | NULL | | {bank_pkey} | false location | STRING | true | NULL | | {bank_pkey} | false currency | STRING | true | NULL | | {bank_pkey} | false (6 rows) ``` #### Add a column with a `NOT NULL` constraint and a `DEFAULT` value ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE bank ADD COLUMN interest DECIMAL NOT NULL DEFAULT (DECIMAL '1.3'); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM bank; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------+-----------+-------------+------------------------+-----------------------+-------------+------------ id | INT8 | false | NULL | | {bank_pkey} | false balance | INT8 | true | NULL | | {bank_pkey} | false payload | STRING | true | NULL | | {bank_pkey} | false active | BOOL | true | NULL | | {bank_pkey} | false location | STRING | true | NULL | | {bank_pkey} | false currency | STRING | true | NULL | | {bank_pkey} | false interest | DECIMAL | false | 1.3:::DECIMAL::DECIMAL | | {bank_pkey} | false (7 rows) ``` #### Add a column with a `UNIQUE` constraint ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE bank ADD COLUMN address STRING UNIQUE; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM bank; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------+-----------+-------------+----------------+-----------------------+------------------------------+------------ id | INT8 | false | NULL | | {bank_address_key,bank_pkey} | false balance | INT8 | true | NULL | | {bank_pkey} | false payload | STRING | true | NULL | | {bank_pkey} | false active | BOOL | true | NULL | | {bank_pkey} | false location | STRING | true | NULL | | {bank_pkey} | false currency | STRING | true | NULL | | {bank_pkey} | false interest | DECIMAL | false | 1.3:::DECIMAL | | {bank_pkey} | false address | STRING | true | NULL | | {bank_address_key,bank_pkey} | false (8 rows) ``` #### Add a column with a `FOREIGN KEY` constraint ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE customers ( id INT PRIMARY KEY, name STRING ); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE bank ADD COLUMN cust_number INT REFERENCES customers(id); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM bank; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------+-----------+-------------+----------------+-----------------------+------------------------------+------------ id | INT8 | false | NULL | | {bank_address_key,bank_pkey} | false balance | INT8 | true | NULL | | {bank_pkey} | false payload | STRING | true | NULL | | {bank_pkey} | false active | BOOL | true | NULL | | {bank_pkey} | false location | STRING | true | NULL | | {bank_pkey} | false currency | STRING | true | NULL | | {bank_pkey} | false interest | DECIMAL | false | 1.3:::DECIMAL | | {bank_pkey} | false address | STRING | true | NULL | | {bank_address_key,bank_pkey} | false cust_number | INT8 | true | NULL | | {bank_pkey} | false (9 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CONSTRAINTS FROM bank; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | constraint_name | constraint_type | details | validated -------------+-----------------------+-----------------+----------------------------------------------------+------------ bank | bank_address_key | UNIQUE | UNIQUE (address ASC) | t bank | bank_cust_number_fkey | FOREIGN KEY | FOREIGN KEY (cust_number) REFERENCES customers(id) | t bank | bank_pkey | PRIMARY KEY | PRIMARY KEY (id ASC) | t (3 rows) ``` #### Add a column with collation ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE bank ADD COLUMN more_names STRING COLLATE en; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM bank; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------+-------------------+-------------+----------------+-----------------------+------------------------------+------------ id | INT8 | false | NULL | | {bank_address_key,bank_pkey} | false balance | INT8 | true | NULL | | {bank_pkey} | false payload | STRING | true | NULL | | {bank_pkey} | false active | BOOL | true | NULL | | {bank_pkey} | false location | STRING | true | NULL | | {bank_pkey} | false currency | STRING | true | NULL | | {bank_pkey} | false interest | DECIMAL | false | 1.3:::DECIMAL | | {bank_pkey} | false address | STRING | true | NULL | | {bank_address_key,bank_pkey} | false cust_number | INT8 | true | NULL | | {bank_pkey} | false more_names | STRING COLLATE en | true | NULL | | {bank_pkey} | false (10 rows) ``` #### Add a column and assign it to a column family ##### Add a column and assign it to a new column family ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE bank ADD COLUMN location1 STRING CREATE FAMILY f1; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE TABLE bank; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement -------------+-------------------------------------------------------------------------------------------------------------------------------------- bank | CREATE TABLE bank ( | id INT8 NOT NULL, | balance INT8 NULL, | payload STRING NULL, | active BOOL NULL, | location STRING NULL, | currency STRING NULL, | interest DECIMAL NOT NULL DEFAULT 1.3:::DECIMAL, | address STRING NULL, | cust_number INT8 NULL, | more_names STRING COLLATE en NULL, | location1 STRING NULL, | CONSTRAINT bank_pkey PRIMARY KEY (id ASC), | CONSTRAINT fk_cust_number_ref_customers FOREIGN KEY (cust_number) REFERENCES customers(id), | UNIQUE INDEX bank_address_key (address ASC), | FAMILY fam_0_id_balance_payload (id, balance, payload, active, location, currency, interest, address, cust_number, more_names), | FAMILY f1 (location1) | ) (1 row) ``` ##### Add a column and assign it to an existing column family ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE bank ADD COLUMN location2 STRING FAMILY f1; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE TABLE bank; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement -------------+-------------------------------------------------------------------------------------------------------------------------------------- bank | CREATE TABLE bank ( | id INT8 NOT NULL, | balance INT8 NULL, | payload STRING NULL, | active BOOL NULL, | location STRING NULL, | currency STRING NULL, | interest DECIMAL NOT NULL DEFAULT 1.3:::DECIMAL, | address STRING NULL, | cust_number INT8 NULL, | more_names STRING COLLATE en NULL, | location1 STRING NULL, | location2 STRING NULL, | CONSTRAINT bank_pkey PRIMARY KEY (id ASC), | CONSTRAINT fk_cust_number_ref_customers FOREIGN KEY (cust_number) REFERENCES customers(id), | UNIQUE INDEX bank_address_key (address ASC), | FAMILY fam_0_id_balance_payload (id, balance, payload, active, location, currency, interest, address, cust_number, more_names), | FAMILY f1 (location1, location2) | ) (1 row) ``` ##### Add a column and create a new column family if column family does not exist ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE bank ADD COLUMN new_name STRING CREATE IF NOT EXISTS FAMILY f2; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE TABLE bank; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement -------------+-------------------------------------------------------------------------------------------------------------------------------------- bank | CREATE TABLE bank ( | id INT8 NOT NULL, | balance INT8 NULL, | payload STRING NULL, | active BOOL NULL, | location STRING NULL, | currency STRING NULL, | interest DECIMAL NOT NULL DEFAULT 1.3:::DECIMAL, | address STRING NULL, | cust_number INT8 NULL, | more_names STRING COLLATE en NULL, | location1 STRING NULL, | location2 STRING NULL, | new_name STRING NULL, | CONSTRAINT bank_pkey PRIMARY KEY (id ASC), | CONSTRAINT fk_cust_number_ref_customers FOREIGN KEY (cust_number) REFERENCES customers(id), | UNIQUE INDEX bank_address_key (address ASC), | FAMILY fam_0_id_balance_payload (id, balance, payload, active, location, currency, interest, address, cust_number, more_names), | FAMILY f1 (location1, location2), | FAMILY f2 (new_name) | ) (1 row) ``` ##### Move a column from one column family to another Moving frequently updated columns to their own column family can increase performance. To move a column from one column family to another column family, create a temporary, non-visible stored computed column in the target column family, then rename the columns. Once this succeeds, you can drop the original, now renamed column. For example, to move the `new_name` column from `f2` to `f1`: 1. Create a temporary computed column in the target column family of the same data type as the column you want to move: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE bank ADD COLUMN newer_name STRING FAMILY f1 NOT VISIBLE AS (new_name) STORED; ``` This causes `newer_name` to have the same values as `new_name`. 2. Rename the columns: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE bank ALTER COLUMN newer_name DROP STORED, ALTER COLUMN newer_name SET VISIBLE, RENAME COLUMN new_name TO old_name, RENAME COLUMN newer_name TO new_name, ALTER COLUMN old_name SET NOT VISIBLE; ``` 3. Drop the old column: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET sql_safe_updates = false; ALTER TABLE bank DROP COLUMN old_name; SET sql_safe_updates = true; ``` You must set the `sql_safe_updates` session variable to `false` to drop a column in a table that has data. Moving a column to another column family executes writes to the underlying storage equal to two times the number of rows. For example, if the table has 10 million rows, there will be 20 million writes to the storage layer: 10 million writes when creating the temporary stored computed column, and 10 million writes when removing the original column. #### Add a column with an `ON UPDATE` expression `ON UPDATE` expressions set the value for a column when other values in a row are updated. For example, suppose you add a new column to the `bank` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE bank ADD COLUMN last_updated TIMESTAMPTZ DEFAULT now() ON UPDATE now(); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT id, balance, last_updated FROM bank LIMIT 5; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | balance | last_updated -----+---------+-------------------------------- 0 | 0 | 2021-10-21 17:03:41.213557+00 1 | 0 | 2021-10-21 17:03:41.213557+00 2 | 0 | 2021-10-21 17:03:41.213557+00 3 | 0 | 2021-10-21 17:03:41.213557+00 4 | 0 | 2021-10-21 17:03:41.213557+00 (5 rows) ``` When any value in any row of the `bank` table is updated, CockroachDB re-evaluates the `ON UPDATE` expression and updates the `last_updated` column with the result. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > UPDATE bank SET balance = 500 WHERE id = 0; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT id, balance, last_updated FROM bank LIMIT 5; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | balance | last_updated -----+---------+-------------------------------- 0 | 500 | 2021-10-21 17:06:42.211261+00 1 | 0 | 2021-10-21 17:03:41.213557+00 2 | 0 | 2021-10-21 17:03:41.213557+00 3 | 0 | 2021-10-21 17:03:41.213557+00 4 | 0 | 2021-10-21 17:03:41.213557+00 (5 rows) ``` ### Add constraints #### Setup To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo ``` #### Add the `UNIQUE` constraint Adding the `UNIQUE` constraint requires that all of a column's values be distinct from one another (except for `NULL` values). ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users ADD CONSTRAINT id_name_unique UNIQUE (id, name); ``` #### Add the `CHECK` constraint Adding the `CHECK` constraint requires that all of a column's values evaluate to `TRUE` for a Boolean expression. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE rides ADD CONSTRAINT check_revenue_positive CHECK (revenue >= 0); ``` In the process of adding the constraint CockroachDB will run a background job to validate existing table data. If CockroachDB finds a row that violates the constraint during the validation step, the [`ADD CONSTRAINT`](#add-constraint) statement will fail. ##### Add constraints to columns created during a transaction You can add check constraints to columns that were created earlier in the transaction. For example: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > BEGIN; > ALTER TABLE users ADD COLUMN is_owner STRING; > ALTER TABLE users ADD CONSTRAINT check_is_owner CHECK (is_owner IN ('yes', 'no', 'unknown')); > COMMIT; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} BEGIN ALTER TABLE ALTER TABLE COMMIT ``` The entire transaction will be rolled back, including any new columns that were added, in the following cases: * If an existing column is found containing values that violate the new constraint. * If a new column has a default value or is a computed column that would have contained values that violate the new constraint. #### Add the foreign key constraint with `CASCADE` To add a foreign key constraint, use the following steps. Given two tables, `users` and `vehicles`, without foreign key constraints: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement -------------+-------------------------------------------------------------- users | CREATE TABLE users ( | id UUID NOT NULL, | city VARCHAR NOT NULL, | name VARCHAR NULL, | address VARCHAR NULL, | credit_card VARCHAR NULL, | CONSTRAINT users_pkey PRIMARY KEY (city ASC, id ASC) | ) (1 row) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE vehicles; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement -------------+------------------------------------------------------------------------------------------------ vehicles | CREATE TABLE vehicles ( | id UUID NOT NULL, | city VARCHAR NOT NULL, | type VARCHAR NULL, | owner_id UUID NULL, | creation_time TIMESTAMP NULL, | status VARCHAR NULL, | current_location VARCHAR NULL, | ext JSONB NULL, | CONSTRAINT vehicles_pkey PRIMARY KEY (city ASC, id ASC), | ) (1 row) ``` You can include a foreign key action to specify what happens when a foreign key is updated or deleted. Using `ON DELETE CASCADE` will ensure that when the referenced row is deleted, all dependent objects are also deleted. `CASCADE` does not list the objects it drops or updates, so it should be used with caution. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE vehicles ADD CONSTRAINT users_fk FOREIGN KEY (city, owner_id) REFERENCES users (city, id) ON DELETE CASCADE; ``` For an example of validating this constraint, see [Validate a constraint](#validate-a-constraint). By default, referenced columns must be in the same database as the referencing foreign key column. To enable cross-database foreign key references, set the `sql.cross_db_fks.enabled` cluster setting to `true`. #### Drop and add a primary key constraint Suppose that you want to add `creation_time` to the composite primary key of the `promo_codes` table, [without creating a secondary index of the existing primary key](#changing-primary-keys-with-add-constraint-primary-key). To do so, use [`DROP CONSTRAINT`](#drop-constraint) and [`ADD CONSTRAINT`](#add-constraint) in a single `ALTER TABLE` statement. 1. View the details of the `promo_codes` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW CREATE TABLE promo_codes; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement --------------+------------------------------------------------------------ promo_codes | CREATE TABLE public.promo_codes ( | code VARCHAR NOT NULL, | description VARCHAR NULL, | creation_time TIMESTAMP NULL, | expiration_time TIMESTAMP NULL, | rules JSONB NULL, | CONSTRAINT promo_codes_pkey PRIMARY KEY (code ASC) | ) (1 row) ``` 2. Add a `NOT NULL` constraint to the `creation_time` column with [`ALTER COLUMN`](#alter-column): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE promo_codes ALTER COLUMN creation_time SET NOT NULL; ``` 3. To issue the schema change atomically, use single statements as an implicit transaction. `DROP CONSTRAINT` and `ADD CONSTRAINT` can be combined in a single `ALTER TABLE` statement: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE promo_codes DROP CONSTRAINT promo_codes_pkey, ADD CONSTRAINT promo_codes_pkey PRIMARY KEY (code, creation_time); ``` You should **not** execute the schema change with multiple statements within an explicit transaction. Refer to Schema changes within transactions. 4. View the updated table structure: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW CREATE TABLE promo_codes; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement --------------+---------------------------------------------------------------------------- promo_codes | CREATE TABLE public.promo_codes ( | code VARCHAR NOT NULL, | description VARCHAR NULL, | creation_time TIMESTAMP NOT NULL, | expiration_time TIMESTAMP NULL, | rules JSONB NULL, | CONSTRAINT promo_codes_pkey PRIMARY KEY (code ASC, creation_time ASC) | ) (1 row) ``` Using `ALTER PRIMARY KEY` would have created a `UNIQUE` secondary index called `promo_codes_code_key`. Instead, there is just one index for the primary key constraint. #### Add a unique index to a `REGIONAL BY ROW` table In multi-region deployments, most users should use `REGIONAL BY ROW` tables instead of explicit index partitioning. When you add an index to a `REGIONAL BY ROW` table, it is automatically partitioned on the `crdb_region` column. Explicit index partitioning is not required. While CockroachDB process an `ADD REGION` or `DROP REGION` statement on a particular database, creating or modifying an index will throw an error. Similarly, all `ADD REGION` and `DROP REGION` statements will be blocked while an index is being modified on a `REGIONAL BY ROW` table within the same database. This example assumes you have a simulated multi-region database running on your local machine following the steps described in Low Latency Reads and Writes in a Multi-Region Cluster. It shows how a `UNIQUE` index is partitioned, but it's similar to how all indexes are partitioned on `REGIONAL BY ROW` tables. To show how the automatic partitioning of indexes on `REGIONAL BY ROW` tables works, we will: 1. Add a column to the `users` table in the MovR dataset. 2. Add a `UNIQUE` constraint to that column. 3. Verify that the index is automatically partitioned for better multi-region performance by using `SHOW INDEXES` and `SHOW PARTITIONS`. First, add a column and its unique constraint. We'll use `email` since that is something that should be unique per user. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE users ADD COLUMN email STRING; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE users ADD CONSTRAINT user_email_unique UNIQUE (email); ``` Next, issue the `SHOW INDEXES` statement. You will see that [the implicit region column](#set-the-table-locality-to-regional-by-row) that was added when the table was converted to regional by row is now indexed: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW INDEXES FROM 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 | region | ASC | f | t | t users | users_pkey | f | 2 | id | ASC | f | f | t users | users_pkey | f | 3 | city | N/A | t | f | t users | users_pkey | f | 4 | name | N/A | t | f | t users | users_pkey | f | 5 | address | N/A | t | f | t users | users_pkey | f | 6 | credit_card | N/A | t | f | t users | users_pkey | f | 7 | email | N/A | t | f | t users | user_email_unique | f | 1 | region | ASC | f | t | t users | user_email_unique | f | 2 | email | ASC | f | f | t users | user_email_unique | f | 3 | id | ASC | f | t | t users | users_city_idx | t | 1 | region | ASC | f | t | t users | users_city_idx | t | 2 | city | ASC | f | f | t users | users_city_idx | t | 3 | id | ASC | f | t | t (13 rows) ``` Next, issue the `SHOW PARTITIONS` statement. The following output (which is edited for length) will verify that the unique index was automatically partitioned for you. It shows that the `user_email_unique` index is now partitioned by the database regions `europe-west1`, `us-east1`, and `us-west1`. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW PARTITIONS FROM TABLE users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} database_name | table_name | partition_name | column_names | index_name | partition_value | ... ----------------+------------+----------------+--------------+-------------------------+------------------+----- movr | users | europe-west1 | region | users@user_email_unique | ('europe-west1') | ... movr | users | us-east1 | region | users@user_email_unique | ('us-east1') | ... movr | users | us-west1 | region | users@user_email_unique | ('us-west1') | ... ``` To ensure that the uniqueness constraint is enforced properly across regions when rows are inserted, or the `email` column of an existing row is updated, the database needs to do the following additional work when indexes are partitioned: 1. Run a one-time-only validation query to ensure that the existing data in the table satisfies the unique constraint. 2. Thereafter, the optimizer will automatically add a "uniqueness check" when necessary to any `INSERT`, `UPDATE`, or `UPSERT` statement affecting the columns in the unique constraint. Note that the SQL engine will avoid sending requests to nodes in other regions when it can instead read a value from a unique column that is stored locally. This capability is known as *locality optimized search*. #### Using `DEFAULT gen_random_uuid()` in `REGIONAL BY ROW` tables To auto-generate unique row identifiers in `REGIONAL BY ROW` tables, use the `UUID` column with the `gen_random_uuid()` function as the default value: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE users ( id UUID NOT NULL DEFAULT gen_random_uuid(), city STRING NOT NULL, name STRING NULL, address STRING NULL, credit_card STRING NULL, CONSTRAINT users_pkey PRIMARY KEY (city ASC, id ASC) ); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > INSERT INTO users (name, city) VALUES ('Petee', 'new york'), ('Eric', 'seattle'), ('Dan', 'seattle'); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT * FROM users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | city | name | address | credit_card +--------------------------------------+----------+-------+---------+-------------+ cf8ee4e2-cd74-449a-b6e6-a0fb2017baa4 | new york | Petee | NULL | NULL 2382564e-702f-42d9-a139-b6df535ae00a | seattle | Eric | NULL | NULL 7d27e40b-263a-4891-b29b-d59135e55650 | seattle | Dan | NULL | NULL (3 rows) ``` When using `DEFAULT gen_random_uuid()` on columns in `REGIONAL BY ROW` tables, uniqueness checks on those columns are disabled by default for performance purposes. CockroachDB assumes uniqueness based on the way this column generates `UUIDs`. To enable this check, you can modify the `sql.optimizer.uniqueness_checks_for_gen_random_uuid.enabled` cluster setting. Note that while there is virtually no chance of a [collision](https://wikipedia.org/wiki/Universally_unique_identifier#Collisions) occurring when enabling this setting, it is not truly zero. #### Using implicit vs. explicit index partitioning in `REGIONAL BY ROW` tables In `REGIONAL BY ROW` tables, all indexes are partitioned on the region column (usually called `crdb_region`). These indexes can either include or exclude the partitioning key (`crdb_region`) as the first column in the index definition: * If `crdb_region` is included in the index definition, a `UNIQUE` index will enforce uniqueness on the set of columns, just like it would in a non-partitioned table. * If `crdb_region` is excluded from the index definition, that serves as a signal that CockroachDB should enforce uniqueness on only the columns in the index definition. In the latter case, the index alone cannot enforce uniqueness on columns that are not a prefix of the index columns, so any time rows are inserted or updated in a `REGIONAL BY ROW` table that has an implicitly partitioned `UNIQUE` index, the optimizer must add uniqueness checks. Whether or not to explicitly include `crdb_region` in the index definition depends on the context: * If you only need to enforce uniqueness at the region level, then including `crdb_region` in the `UNIQUE` index definition will enforce these semantics and allow you to get better performance on `INSERT` s, `UPDATE` s, and `UPSERT` s, since there will not be any added latency from uniqueness checks. * If you need to enforce global uniqueness, you should not include `crdb_region` in the `UNIQUE` (or `PRIMARY KEY` ) index definition, and the database will automatically ensure that the constraint is enforced. To illustrate the different behavior of explicitly vs. implicitly partitioned indexes, we will perform the following tasks: * Create a schema that includes an explicitly partitioned index, and an implicitly partitioned index. * Check the output of several queries using `EXPLAIN` to show the differences in behavior between the two. 1. Start `cockroach demo` as follows: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} cockroach demo --geo-partitioned-replicas ``` 2. Create a multi-region database and an `employees` table. There are three indexes in the table, all `UNIQUE` and all partitioned by the `crdb_region` column. The table schema guarantees that both `id` and `email` are globally unique, while `desk_id` is only unique per region. The indexes on `id` and `email` are implicitly partitioned, while the index on `(crdb_region, desk_id)` is explicitly partitioned. `UNIQUE` indexes can only directly enforce uniqueness on all columns in the index, including partitioning columns, so each of these indexes enforce uniqueness for `id`, `email`, and `desk_id` per region, respectively. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE DATABASE multi_region_test_db PRIMARY REGION "europe-west1" REGIONS "us-west1", "us-east1"; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} USE multi_region_test_db; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE employee ( id INT PRIMARY KEY, email STRING UNIQUE, desk_id INT, UNIQUE (crdb_region, desk_id) ) LOCALITY REGIONAL BY ROW; ``` 3. In the following statement, we add a new user with the required `id`, `email`, and `desk_id` columns. CockroachDB needs to do additional work to enforce global uniqueness for the `id` and `email` columns, which are implicitly partitioned. This additional work is in the form of "uniqueness checks" that the optimizer adds as part of mutation queries. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} EXPLAIN INSERT INTO employee VALUES (1, 'joe@example.com', 1); ``` The following `EXPLAIN` output shows that the optimizer has added two `constraint-check` post queries to check the uniqueness of the implicitly partitioned indexes `id` and `email`. There is no check needed for `desk_id` (really `(crdb_region, desk_id)`), since that constraint is automatically enforced by the explicitly partitioned index we added in the preceding `CREATE TABLE` statement. ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} info -------------------------------------------------------------------------------------- distribution: local vectorized: true • root │ ├── • insert │ │ into: employee(id, email, desk_id, crdb_region) │ │ │ └── • buffer │ │ label: buffer 1 │ │ │ └── • values │ size: 5 columns, 1 row │ ├── • constraint-check │ │ │ └── • error if rows │ │ │ └── • lookup join (semi) │ │ table: employee@primary │ │ equality: (lookup_join_const_col_@15, column1) = (crdb_region,id) │ │ equality cols are key │ │ pred: column10 != crdb_region │ │ │ └── • cross join │ │ estimated row count: 3 │ │ │ ├── • values │ │ size: 1 column, 3 rows │ │ │ └── • scan buffer │ label: buffer 1 │ └── • constraint-check │ └── • error if rows │ └── • lookup join (semi) │ table: employee@employee_email_key │ equality: (lookup_join_const_col_@25, column2) = (crdb_region,email) │ equality cols are key │ pred: (column1 != id) OR (column10 != crdb_region) │ └── • cross join │ estimated row count: 3 │ ├── • values │ size: 1 column, 3 rows │ └── • scan buffer label: buffer 1 ``` 4. The following statement updates the user's `email` column. Because the unique index on the `email` column is implicitly partitioned, the optimizer must perform a uniqueness check. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} EXPLAIN UPDATE employee SET email = 'joe1@example.com' WHERE id = 1; ``` In the following `EXPLAIN` output, the optimizer performs a uniqueness check for `email` since we're not updating any other columns (see the `constraint-check` section). ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} info -------------------------------------------------------------------------------------------------------- distribution: local vectorized: true • root │ ├── • update │ │ table: employee │ │ set: email │ │ │ └── • buffer │ │ label: buffer 1 │ │ │ └── • render │ │ estimated row count: 1 │ │ │ └── • union all │ │ estimated row count: 1 │ │ limit: 1 │ │ │ ├── • scan │ │ estimated row count: 1 (100% of the table; stats collected 1 minute ago) │ │ table: employee@primary │ │ spans: [/'us-east1'/1 - /'us-east1'/1] │ │ │ └── • scan │ estimated row count: 1 (100% of the table; stats collected 1 minute ago) │ table: employee@primary │ spans: [/'europe-west1'/1 - /'europe-west1'/1] [/'us-west1'/1 - /'us-west1'/1] │ └── • constraint-check │ └── • error if rows │ └── • lookup join (semi) │ table: employee@employee_email_key │ equality: (lookup_join_const_col_@18, email_new) = (crdb_region,email) │ equality cols are key │ pred: (id != id) OR (crdb_region != crdb_region) │ └── • cross join │ estimated row count: 3 │ ├── • values │ size: 1 column, 3 rows │ └── • scan buffer label: buffer 1 ``` 5. If we only update the user's `desk_id`, no uniqueness checks are needed, since the index on that column is explicitly partitioned (it's really `(crdb_region, desk_id)`). ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} EXPLAIN UPDATE employee SET desk_id = 2 WHERE id = 1; ``` Because no uniqueness check is needed, there is no `constraint-check` section in the `EXPLAIN` output. ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} info ------------------------------------------------------------------------------------------------ distribution: local vectorized: true • update │ table: employee │ set: desk_id │ auto commit │ └── • render │ estimated row count: 1 │ └── • union all │ estimated row count: 1 │ limit: 1 │ ├── • scan │ estimated row count: 1 (100% of the table; stats collected 2 minutes ago) │ table: employee@primary │ spans: [/'us-east1'/1 - /'us-east1'/1] │ └── • scan estimated row count: 1 (100% of the table; stats collected 2 minutes ago) table: employee@primary spans: [/'europe-west1'/1 - /'europe-west1'/1] [/'us-west1'/1 - /'us-west1'/1] ``` ### Alter columns #### Set or change a `DEFAULT` value Setting the `DEFAULT` value constraint inserts the value when data's written to the table without explicitly defining the value for the column. If the column already has a `DEFAULT` value set, you can use this statement to change it. The following example inserts the Boolean value `true` whenever you inserted data to the `subscriptions` table without defining a value for the `newsletter` column. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE subscriptions ALTER COLUMN newsletter SET DEFAULT true; ``` #### Remove `DEFAULT` constraint If the column has a defined `DEFAULT` value, you can remove the constraint, which means the column will no longer insert a value by default if one is not explicitly defined for the column. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE subscriptions ALTER COLUMN newsletter DROP DEFAULT; ``` #### Set `NOT NULL` constraint To specify that the column cannot contain `NULL` values, set the `NOT NULL` constraint. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE subscriptions ALTER COLUMN newsletter SET NOT NULL; ``` #### Remove `NOT NULL` constraint If the column has the `NOT NULL` constraint applied to it, you can remove the constraint, which means the column becomes optional and can have `NULL` values written into it. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE subscriptions ALTER COLUMN newsletter DROP NOT NULL; ``` #### Convert a computed column into a regular column You can convert a stored, computed column into a regular column by using `ALTER TABLE`. In this example, create a simple table with a computed column: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE office_dogs ( id INT PRIMARY KEY, first_name STRING, last_name STRING, full_name STRING AS (CONCAT(first_name, ' ', last_name)) STORED ); ``` Then, insert a few rows of data: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > INSERT INTO office_dogs (id, first_name, last_name) VALUES (1, 'Petee', 'Hirata'), (2, 'Carl', 'Kimball'), (3, 'Ernie', 'Narayan'); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT * FROM office_dogs; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} +----+------------+-----------+---------------+ | id | first_name | last_name | full_name | +----+------------+-----------+---------------+ | 1 | Petee | Hirata | Petee Hirata | | 2 | Carl | Kimball | Carl Kimball | | 3 | Ernie | Narayan | Ernie Narayan | +----+------------+-----------+---------------+ (3 rows) ``` The `full_name` column is computed from the `first_name` and `last_name` columns without the need to define a view. You can view the column details with the `SHOW COLUMNS` statement: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM office_dogs; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} +-------------+-----------+-------------+----------------+------------------------------------+-------------+ | column_name | data_type | is_nullable | column_default | generation_expression | indices | +-------------+-----------+-------------+----------------+------------------------------------+-------------+ | id | INT | false | NULL | | {"primary"} | | first_name | STRING | true | NULL | | {} | | last_name | STRING | true | NULL | | {} | | full_name | STRING | true | NULL | concat(first_name, ' ', last_name) | {} | +-------------+-----------+-------------+----------------+------------------------------------+-------------+ (4 rows) ``` Now, convert the computed column (`full_name`) to a regular column: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE office_dogs ALTER COLUMN full_name DROP STORED; ``` Check that the computed column was converted: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM office_dogs; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} +-------------+-----------+-------------+----------------+-----------------------+-------------+ | column_name | data_type | is_nullable | column_default | generation_expression | indices | +-------------+-----------+-------------+----------------+-----------------------+-------------+ | id | INT | false | NULL | | {"primary"} | | first_name | STRING | true | NULL | | {} | | last_name | STRING | true | NULL | | {} | | full_name | STRING | true | NULL | | {} | +-------------+-----------+-------------+----------------+-----------------------+-------------+ (4 rows) ``` The computed column is now a regular column and can be updated as such: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > INSERT INTO office_dogs (id, first_name, last_name, full_name) VALUES (4, 'Lola', 'McDog', 'This is not computed'); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT * FROM office_dogs; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} +----+------------+-----------+----------------------+ | id | first_name | last_name | full_name | +----+------------+-----------+----------------------+ | 1 | Petee | Hirata | Petee Hirata | | 2 | Carl | Kimball | Carl Kimball | | 3 | Ernie | Narayan | Ernie Narayan | | 4 | Lola | McDog | This is not computed | +----+------------+-----------+----------------------+ (4 rows) ``` #### Alter the formula for a computed column To alter the formula for a computed column, you must `DROP` and `ADD` the column back with the new definition. Take the following table for instance: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE x ( a INT NULL, b INT NULL AS (a * 2) STORED, c INT NULL AS (a + 4) STORED, FAMILY "primary" (a, b, rowid, c) ); ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE Time: 4ms total (execution 4ms / network 0ms) ``` Add a computed column `d`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE x ADD COLUMN d INT AS (a // 2) STORED; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE Time: 199ms total (execution 199ms / network 0ms) ``` If you try to alter it, you'll get an error: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE x ALTER COLUMN d INT AS (a // 3) STORED; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} invalid syntax: statement ignored: at or near "int": syntax error SQLSTATE: 42601 DETAIL: source SQL: ALTER TABLE x ALTER COLUMN d INT AS (a // 3) STORED ^ HINT: try \h ALTER TABLE ``` However, you can drop it and then add it with the new definition: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SET sql_safe_updates = false; > ALTER TABLE x DROP COLUMN d; > ALTER TABLE x ADD COLUMN d INT AS (a // 3) STORED; > SET sql_safe_updates = true; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET Time: 1ms total (execution 0ms / network 0ms) ALTER TABLE Time: 195ms total (execution 195ms / network 0ms) ALTER TABLE Time: 186ms total (execution 185ms / network 0ms) SET Time: 0ms total (execution 0ms / network 0ms) ``` #### Convert to a different data type The TPC-C database has a `customer` table with a column `c_credit_lim` of type `DECIMAL(10,2)`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > WITH x AS (SHOW COLUMNS FROM customer) SELECT column_name, data_type FROM x WHERE column_name='c_credit_lim'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type ---------------+---------------- c_credit_lim | DECIMAL(10,2) (1 row) ``` To change the data type from `DECIMAL` to `STRING`: 1. Set the `enable_experimental_alter_column_type_general` session variable to `true`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SET enable_experimental_alter_column_type_general = true; ``` 2. Alter the column type: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE customer ALTER c_credit_lim TYPE STRING; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} NOTICE: ALTER COLUMN TYPE changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes; some writes to the altered column may be rejected until the schema change is finalized ``` 3. Verify the type: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > WITH x AS (SHOW COLUMNS FROM customer) SELECT column_name, data_type FROM x WHERE column_name='c_credit_lim'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type ---------------+------------ c_credit_lim | STRING (1 row) ``` #### Change a column type's precision The TPC-C `customer` table contains a column `c_balance` of type `DECIMAL(12,2)`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > WITH x AS (SHOW COLUMNS FROM customer) SELECT column_name, data_type FROM x WHERE column_name='c_balance'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type --------------+---------------- c_balance | DECIMAL(12,2) (1 row) ``` To increase the precision of the `c_balance` column from `DECIMAL(12,2)` to `DECIMAL(14,2)`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE customer ALTER c_balance TYPE DECIMAL(14,2); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > WITH x AS (SHOW COLUMNS FROM customer) SELECT column_name, data_type FROM x WHERE column_name='c_balance'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type --------------+---------------- c_balance | DECIMAL(14,2) (1 row) ``` #### Change a column's type using an expression You can change the data type of a column and create a new, computed value from the old column values, with a [`USING` clause](#parameters). For example: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > WITH x AS (SHOW COLUMNS FROM customer) SELECT column_name, data_type FROM x WHERE column_name='c_discount'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type --------------+--------------- c_discount | DECIMAL(4,4) (1 row) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT c_discount FROM customer LIMIT 10; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} c_discount -------------- 0.1569 0.4629 0.2932 0.0518 0.3922 0.1106 0.0622 0.4916 0.3072 0.0316 (10 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE customer ALTER c_discount TYPE STRING USING ((c_discount*100)::DECIMAL(4,2)::STRING || ' percent'); ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} NOTICE: ALTER COLUMN TYPE changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes; some writes to the altered column may be rejected until the schema change is finalized ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > WITH x AS (SHOW COLUMNS FROM customer) SELECT column_name, data_type FROM x WHERE column_name='c_discount'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type --------------+------------ c_discount | STRING (1 row) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT c_discount FROM customer LIMIT 10; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} c_discount ----------------- 15.69 percent 46.29 percent 29.32 percent 5.18 percent 39.22 percent 11.06 percent 6.22 percent 49.16 percent 30.72 percent 3.16 percent (10 rows) ``` #### Set the visibility of a column To specify that a column won't be returned when using `*` in a `SELECT` clause, set the `NOT VISIBLE` property. You can set the `NOT VISIBLE` property only on individual columns. For example, the `users` table of the `movr` database contains the `credit_card` column. If you don't want users to see that column when running `SELECT * FROM users;`, you can hide it as follows: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users ALTER COLUMN credit_card SET NOT VISIBLE; ``` When you run `SELECT *`, the column does not appear: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT * FROM users WHERE city = 'rome'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | city | name | address ---------------------------------------+------+-------------------+-------------------------------- e6666666-6666-4800-8000-00000000002d | rome | Misty Adams | 82289 Natasha River Suite 12 eb851eb8-51eb-4800-8000-00000000002e | rome | Susan Morse | 49364 Melissa Squares Suite 4 f0a3d70a-3d70-4000-8000-00000000002f | rome | Victoria Jennings | 31562 Krista Squares Suite 62 f5c28f5c-28f5-4000-8000-000000000030 | rome | Eric Perez | 57624 Kelly Forks fae147ae-147a-4000-8000-000000000031 | rome | Richard Bullock | 21194 Alexander Estate (5 rows) ``` The column is still selectable if you name it directly in the `target_elem` parameter: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT id, credit_card FROM users WHERE city = 'rome'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | credit_card ---------------------------------------+-------------- e6666666-6666-4800-8000-00000000002d | 4418943046 eb851eb8-51eb-4800-8000-00000000002e | 0655485426 f0a3d70a-3d70-4000-8000-00000000002f | 2232698265 f5c28f5c-28f5-4000-8000-000000000030 | 2620636730 fae147ae-147a-4000-8000-000000000031 | 2642076323 (5 rows) ``` To unhide the column, run: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users ALTER COLUMN credit_card SET VISIBLE; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT * from user WHERE city = 'rome'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | city | name | address | credit_card ---------------------------------------+------+-------------------+-------------------------------+-------------- e6666666-6666-4800-8000-00000000002d | rome | Misty Adams | 82289 Natasha River Suite 12 | 4418943046 eb851eb8-51eb-4800-8000-00000000002e | rome | Susan Morse | 49364 Melissa Squares Suite 4 | 0655485426 f0a3d70a-3d70-4000-8000-00000000002f | rome | Victoria Jennings | 31562 Krista Squares Suite 62 | 2232698265 f5c28f5c-28f5-4000-8000-000000000030 | rome | Eric Perez | 57624 Kelly Forks | 2620636730 fae147ae-147a-4000-8000-000000000031 | rome | Richard Bullock | 21194 Alexander Estate | 2642076323 (5 rows) ``` ### Alter a primary key #### Alter a single-column primary key Suppose that you are storing the data for users of your application in a table called `users`, defined by the following `CREATE TABLE` statement: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE users ( name STRING PRIMARY KEY, email STRING ); ``` The primary key of this table is on the `name` column. This is a poor choice, as some users likely have the same name, and all primary keys enforce a `UNIQUE` constraint on row values of the primary key column. Per our best practices, you should instead use a `UUID` for single-column primary keys, and populate the rows of the table with generated, unique values. You can add a column and change the primary key with a couple of `ALTER TABLE` statements: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users ADD COLUMN id UUID NOT NULL DEFAULT gen_random_uuid(); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users ALTER PRIMARY KEY USING COLUMNS (id); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE TABLE users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement -------------+-------------------------------------------------- users | CREATE TABLE users ( | name STRING NOT NULL, | email STRING NULL, | id UUID NOT NULL DEFAULT gen_random_uuid(), | CONSTRAINT users_pkey PRIMARY KEY (id ASC), | UNIQUE INDEX users_name_key (name ASC) | ) (1 row) ``` #### Alter an existing primary key to use hash sharding Let's assume the `events` table already exists: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE events ( product_id INT8, owner UUID, serial_number VARCHAR, event_id UUID, ts TIMESTAMP, data JSONB, PRIMARY KEY (product_id, owner, serial_number, ts, event_id), INDEX (ts) USING HASH ); ``` You can change an existing primary key to use hash sharding by adding the `USING HASH` clause at the end of the key definition: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE events ALTER PRIMARY KEY USING COLUMNS (product_id, owner, serial_number, ts, event_id) USING HASH; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW INDEX FROM events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit -------------+---------------+------------+--------------+-------------------------------------------------------------------+-----------+---------+----------- events | events_pkey | false | 1 | crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | ASC | false | true events | events_pkey | false | 2 | product_id | ASC | false | false events | events_pkey | false | 3 | owner | ASC | false | false events | events_pkey | false | 4 | serial_number | ASC | false | false events | events_pkey | false | 5 | ts | ASC | false | false events | events_pkey | false | 6 | event_id | ASC | false | false events | events_pkey | false | 7 | data | N/A | true | false events | events_ts_idx | true | 1 | crdb_internal_ts_shard_16 | ASC | false | true events | events_ts_idx | true | 2 | ts | ASC | false | false events | events_ts_idx | true | 3 | crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | ASC | false | true events | events_ts_idx | true | 4 | product_id | ASC | false | true events | events_ts_idx | true | 5 | owner | ASC | false | true events | events_ts_idx | true | 6 | serial_number | ASC | false | true events | events_ts_idx | true | 7 | event_id | ASC | false | true (14 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------------------------------------------------------------+-----------+-------------+----------------+-----------------------------------------------------------------------------------------------+-----------------------------+------------ product_id | INT8 | false | NULL | | {events_pkey,events_ts_idx} | false owner | UUID | false | NULL | | {events_pkey,events_ts_idx} | false serial_number | VARCHAR | false | NULL | | {events_pkey,events_ts_idx} | false event_id | UUID | false | NULL | | {events_pkey,events_ts_idx} | false ts | TIMESTAMP | false | NULL | | {events_pkey,events_ts_idx} | false data | JSONB | true | NULL | | {events_pkey} | false crdb_internal_ts_shard_16 | INT8 | false | NULL | mod(fnv32(crdb_internal.datums_to_bytes(ts)), 16) | {events_ts_idx} | true crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | INT8 | false | NULL | mod(fnv32(crdb_internal.datums_to_bytes(event_id, owner, product_id, serial_number, ts)), 16) | {events_pkey,events_ts_idx} | true (8 rows) ``` Note that the old primary key index becomes a secondary index, in this case, `users_name_key`. If you do not want the old primary key to become a secondary index when changing a primary key, you can use `DROP CONSTRAINT`/`ADD CONSTRAINT` instead. ### Configure replication zones #### Setup The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App. To follow along, run `cockroach demo` with the `--geo-partitioned-replicas` flag. This command opens an interactive SQL shell to a temporary, 9-node in-memory cluster with the `movr` database. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo --geo-partitioned-replicas ``` #### Create a replication zone for a table To control replication for a specific table, use the `ALTER TABLE... CONFIGURE ZONE` statement to define the relevant values (other values will be inherited from the parent zone): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CONFIGURE ZONE 1 ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW ZONE CONFIGURATION FROM TABLE users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} target | raw_config_sql --------------+----------------------------------------- TABLE users | ALTER TABLE users CONFIGURE ZONE USING | range_min_bytes = 134217728, | range_max_bytes = 536870912, | gc.ttlseconds = 100000, | num_replicas = 5, | constraints = '[]', | lease_preferences = '[]' (1 row) ``` #### Edit a replication zone ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users CONFIGURE ZONE USING range_min_bytes = 0, range_max_bytes = 90000, gc.ttlseconds = 89999, num_replicas = 4; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CONFIGURE ZONE 1 ``` #### Reset a replication zone ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE t CONFIGURE ZONE USING DEFAULT; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CONFIGURE ZONE 1 ``` #### Remove a replication zone When you discard a zone configuration, the objects it was applied to will then inherit a configuration from an object "the next level up"; e.g., if the object whose configuration is being discarded is a table, it will use its parent database's configuration. You cannot `DISCARD` any zone configurations on multi-region tables, indexes, or partitions if the multi-region abstractions created the zone configuration. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE t CONFIGURE ZONE DISCARD; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CONFIGURE ZONE 1 ``` ### Drop columns #### Setup To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo ``` #### Drop a column If you no longer want a column in a table, you can drop it. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------+-----------+-------------+----------------+-----------------------+-----------+------------ id | UUID | false | NULL | | {primary} | false city | VARCHAR | false | NULL | | {primary} | false name | VARCHAR | true | NULL | | {primary} | false address | VARCHAR | true | NULL | | {primary} | false credit_card | VARCHAR | true | NULL | | {primary} | false (5 rows) ``` If there is data in the table, the `sql_safe_updates` session variable must be set to `false`. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users DROP COLUMN credit_card; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ERROR: rejected (sql_safe_updates = true): ALTER TABLE DROP COLUMN will remove all data in that column SQLSTATE: 01000 ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SET sql_safe_updates = false; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users DROP COLUMN credit_card; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------+-----------+-------------+----------------+-----------------------+-----------+------------ id | UUID | false | NULL | | {primary} | false city | VARCHAR | false | NULL | | {primary} | false name | VARCHAR | true | NULL | | {primary} | false address | VARCHAR | true | NULL | | {primary} | false (4 rows) ``` #### Prevent dropping columns with dependent objects (`RESTRICT`) If the column has dependent objects, such as views, CockroachDB will not drop the column by default. However, if you want to be sure of the behavior you can include the `RESTRICT` clause. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE VIEW expensive_rides AS SELECT id, city FROM rides WHERE revenue > 90; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE rides DROP COLUMN revenue RESTRICT; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ERROR: cannot drop column "revenue" because view "expensive_rides" depends on it SQLSTATE: 2BP01 HINT: you can drop expensive_rides instead. ``` #### Drop a column and its dependent objects (`CASCADE`) If you want to drop the column and all of its dependent options, include the `CASCADE` clause. `CASCADE` does not list objects it drops, so should be used cautiously. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE expensive_rides; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement ------------------+------------------------------------------------------------------------------------------------------------- expensive_rides | CREATE VIEW public.expensive_rides (id, city) AS SELECT id, city FROM movr.public.rides WHERE revenue > 90 (1 row) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE rides DROP COLUMN revenue CASCADE; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE expensive_rides; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ERROR: relation "expensive_rides" does not exist SQLSTATE: 42P01 ``` #### Drop an indexed column `DROP COLUMN` drops a column and any indexes on the column being dropped. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE INDEX start_end_idx ON rides(start_time, end_time); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > WITH x AS (SHOW INDEXES FROM rides) SELECT * FROM x WHERE index_name='start_end_idx'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible -------------+---------------+------------+--------------+-------------+-----------+---------+----------+---------- rides | start_end_idx | t | 1 | start_time | ASC | f | f | t rides | start_end_idx | t | 2 | end_time | ASC | f | f | t rides | start_end_idx | t | 3 | city | ASC | f | t | t rides | start_end_idx | t | 4 | id | ASC | f | t | t (4 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE rides DROP COLUMN start_time; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} NOTICE: the data for dropped indexes is reclaimed asynchronously HINT: The reclamation delay can be customized in the zone configuration for the table. ALTER TABLE ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > WITH x AS (SHOW INDEXES FROM rides) SELECT * FROM x WHERE index_name='start_end_idx'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit -------------+------------+------------+--------------+-------------+-----------+---------+----------- (0 rows) ``` ### Drop constraints #### Setup To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo ``` #### Drop a foreign key constraint ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CONSTRAINTS FROM vehicles; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | constraint_name | constraint_type | details | validated -------------+-------------------+-----------------+---------------------------------------------------------+------------ vehicles | fk_city_ref_users | FOREIGN KEY | FOREIGN KEY (city, owner_id) REFERENCES users(city, id) | true vehicles | vehicles_pkey | PRIMARY KEY | PRIMARY KEY (city ASC, id ASC) | true (2 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE vehicles DROP CONSTRAINT fk_city_ref_users; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CONSTRAINTS FROM vehicles; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | constraint_name | constraint_type | details | validated -------------+-----------------+-----------------+--------------------------------+------------ vehicles | vehicles_pkey | PRIMARY KEY | PRIMARY KEY (city ASC, id ASC) | true (1 row) ``` ### Configure audit logging #### Turn on audit logging Let's say you have a `customers` table that contains personally identifiable information (PII). To turn on audit logs for that table, run the following command: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE customers EXPERIMENTAL_AUDIT SET READ WRITE; ``` Now, every access of customer data is logged to the `SENSITIVE_ACCESS` channel in a `sensitive_table_access` event that looks like the following: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} I210323 18:50:10.951550 1182 8@util/log/event_log.go:32 ⋮ [n1,client=‹[::1]:49851›,hostnossl,user=root] 4 ={"Timestamp":1616525410949087000,"EventType":"sensitive_table_access","Statement":"‹SELECT * FROM \"\".\"\".customers›","User":"‹root›","DescriptorID":52,"ApplicationName":"‹$ cockroach sql›","ExecMode":"exec","NumRows":2,"Age":2.514,"FullTableScan":true,"TxnCounter":38,"TableName":"‹defaultdb.public.customers›","AccessMode":"r"} ``` The preceding example shows the default `crdb-v2` log format. This can be changed to a different format (e.g., JSON). For details, see Configure Logs. For descriptions of all SQL audit event types and their fields, see Notable Event Types. To turn on auditing for more than one table, issue a separate `ALTER` statement for each table. For a more detailed example, see SQL Audit Logging. #### Turn off audit logging To turn off logging, issue the following command: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE customers EXPERIMENTAL_AUDIT SET OFF; ``` ### Change table owner #### Change a table's owner Suppose that the current owner of the `rides` table is `root` and you want to change the owner to a new user named `max`. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE promo_codes OWNER TO max; ``` To verify that the owner is now `max`, query the `pg_catalog.pg_tables` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT tableowner FROM pg_catalog.pg_tables WHERE tablename = 'promo_codes'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} tableowner -------------- max (1 row) ``` If the user running the command is not an admin user, they must own the table and be a member of the new owning role. Also, the new owner role must also have the `CREATE` privilege on the schema to which the table belongs. ### Define partitions #### Define a list partition on a table Suppose we have a table called `students_by_list`, and the primary key of the table is defined as `(country, id)`. We can define partitions on the table by list: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE students_by_list PARTITION BY LIST (country) ( PARTITION north_america VALUES IN ('CA','US'), PARTITION australia VALUES IN ('AU','NZ'), PARTITION DEFAULT VALUES IN (default) ); ``` #### Define a range partition on a table Suppose we have a table called `students_by_range`, and the primary key of the table is defined as `(expected_graduation_date, id)`. We can define partitions on the table by range: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE students_by_range PARTITION BY RANGE (expected_graduation_date) ( PARTITION graduated VALUES FROM (MINVALUE) TO ('2017-08-15'), PARTITION current VALUES FROM ('2017-08-15') TO (MAXVALUE) ); ``` #### Define subpartitions on a table Suppose we have a table named `students`, and the primary key is defined as `(country, expected_graduation_date, id)`. We can define partitions and subpartitions on the table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE students PARTITION BY LIST (country) ( PARTITION australia VALUES IN ('AU','NZ') PARTITION BY RANGE (expected_graduation_date) ( PARTITION graduated_au VALUES FROM (MINVALUE) TO ('2017-08-15'), PARTITION current_au VALUES FROM ('2017-08-15') TO (MAXVALUE) ), PARTITION north_america VALUES IN ('US','CA') PARTITION BY RANGE (expected_graduation_date) ( PARTITION graduated_us VALUES FROM (MINVALUE) TO ('2017-08-15'), PARTITION current_us VALUES FROM ('2017-08-15') TO (MAXVALUE) ) ); ``` #### Repartition a table ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE students_by_range PARTITION BY RANGE (expected_graduation_date) ( PARTITION graduated VALUES FROM (MINVALUE) TO ('2018-08-15'), PARTITION current VALUES FROM ('2018-08-15') TO (MAXVALUE) ); ``` #### Unpartition a table ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE students PARTITION BY NOTHING; ``` ### Rename columns #### Rename a column ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE users ( id INT PRIMARY KEY, first_name STRING, family_name STRING ); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users RENAME COLUMN family_name TO last_name; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement +------------+--------------------------------------------------+ users | CREATE TABLE users ( | id INT8 NOT NULL, | first_name STRING NULL, | last_name STRING NULL, | CONSTRAINT "primary" PRIMARY KEY (id ASC), | FAMILY "primary" (id, first_name, last_name) | ) (1 row) ``` #### Add and rename columns atomically Some subcommands can be used in combination in a single `ALTER TABLE` statement. For example, let's say you create a `users` table with 2 columns, an `id` column for the primary key and a `name` column for each user's last name: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE users ( id INT PRIMARY KEY, name STRING ); ``` Then you decide you want distinct columns for each user's first name, last name, and full name, so you execute a single `ALTER TABLE` statement renaming `name` to `last_name`, adding `first_name`, and adding a computed column called `name` that concatenates `first_name` and `last_name`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users RENAME COLUMN name TO last_name, ADD COLUMN first_name STRING, ADD COLUMN name STRING AS (CONCAT(first_name, ' ', last_name)) STORED; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE TABLE users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement +------------+----------------------------------------------------------------------+ users | CREATE TABLE users ( | id INT8 NOT NULL, | last_name STRING NULL, | first_name STRING NULL, | name STRING NULL AS (concat(first_name, ' ', last_name)) STORED, | CONSTRAINT "primary" PRIMARY KEY (id ASC), | FAMILY "primary" (id, last_name, first_name, name) | ) (1 row) ``` ### Rename constraints #### Rename a constraint ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE logon ( login_id INT PRIMARY KEY, customer_id INT NOT NULL, sales_id INT, UNIQUE (customer_id, sales_id) ); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CONSTRAINTS FROM logon; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | constraint_name | constraint_type | details | validated +------------+--------------------------------+-----------------+----------------------------------------+-----------+ logon | logon_customer_id_sales_id_key | UNIQUE | UNIQUE (customer_id ASC, sales_id ASC) | true logon | logon_pkey | PRIMARY KEY | PRIMARY KEY (login_id ASC) | true (2 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE logon RENAME CONSTRAINT logon_customer_id_sales_id_key TO unique_customer_id_sales_id; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CONSTRAINTS FROM logon; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | constraint_name | constraint_type | details | validated +------------+-----------------------------+-----------------+----------------------------------------+-----------+ logon | logon_pkey | PRIMARY KEY | PRIMARY KEY (login_id ASC) | true logon | unique_customer_id_sales_id | UNIQUE | UNIQUE (customer_id ASC, sales_id ASC) | true (2 rows) ``` ### Rename tables #### Setup To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo ``` #### Rename a table ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} schema_name | table_name | type | estimated_row_count --------------+----------------------------+-------+---------------------- public | promo_codes | table | 1000 public | rides | table | 500 public | user_promo_codes | table | 0 public | users | table | 50 public | vehicle_location_histories | table | 1000 public | vehicles | table | 15 (6 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users RENAME TO riders; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} schema_name | table_name | type | estimated_row_count --------------+----------------------------+-------+---------------------- public | promo_codes | table | 1000 public | rides | table | 500 public | user_promo_codes | table | 0 public | riders | table | 50 public | vehicle_location_histories | table | 1000 public | vehicles | table | 15 (6 rows) ``` To avoid an error in case the table does not exist, you can include `IF EXISTS`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE IF EXISTS customers RENAME TO clients; ``` ### Set and reset storage parameters #### Exclude a table's data from backups In some situations, you may want to exclude a table's row data from a backup. For example, you have a table that contains high-churn data that you would like to garbage collect more quickly than the incremental backup schedule for the database or cluster holding the table. You can use the `exclude_data_from_backup = true` parameter with a `CREATE TABLE` or `ALTER TABLE` statement to mark a table's row data for exclusion from a backup. For more detail and an example through the backup and restore process using this parameter, see Take Full and Incremental Backups. To set the `exclude_data_from_backup` parameter for a table, run the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE movr.user_promo_codes SET (exclude_data_from_backup = true); ``` The `CREATE` statement for this table will now show the parameter set: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW CREATE user_promo_codes; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement -------------------+------------------------------------------------------------------------------------------------------------------ user_promo_codes | CREATE TABLE public.user_promo_codes ( | city VARCHAR NOT NULL, | user_id UUID NOT NULL, | code VARCHAR NOT NULL, | "timestamp" TIMESTAMP NULL, | usage_count INT8 NULL, | CONSTRAINT user_promo_codes_pkey PRIMARY KEY (city ASC, user_id ASC, code ASC), | CONSTRAINT user_promo_codes_city_user_id_fkey FOREIGN KEY (city, user_id) REFERENCES public.users(city, id) | ) WITH (exclude_data_from_backup = true) (1 row) ``` Backups will no longer include the data within the `user_promo_codes` table. The table will still be present in the backup, but it will be empty. To remove this parameter from a table, run: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE movr.user_promo_codes SET (exclude_data_from_backup = false); ``` This will ensure that the table's data is stored in subsequent backups that you take. #### Reset a storage parameter The following `ttl_test` table has three TTL-related storage parameters active on the table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW CREATE TABLE ttl_test; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement -------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ttl_test | CREATE TABLE public.ttl_test ( | id UUID NOT NULL DEFAULT gen_random_uuid(), | description STRING NULL, | inserted_at TIMESTAMP NULL DEFAULT current_timestamp():::TIMESTAMP, | crdb_internal_expiration TIMESTAMPTZ NOT VISIBLE NOT NULL DEFAULT current_timestamp():::TIMESTAMPTZ + '3 mons':::INTERVAL ON UPDATE current_timestamp():::TIMESTAMPTZ + '3 mons':::INTERVAL, | CONSTRAINT ttl_test_pkey PRIMARY KEY (id ASC) | ) WITH (ttl = 'on', ttl_expire_after = '3 mons':::INTERVAL, ttl_job_cron = '@hourly') (1 row) ``` To remove these settings, run the following command: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE ttl_test RESET (ttl); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW CREATE TABLE ttl_test; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement -------------+-------------------------------------------------------------------------- ttl_test | CREATE TABLE public.ttl_test ( | id UUID NOT NULL DEFAULT gen_random_uuid(), | description STRING NULL, | inserted_at TIMESTAMP NULL DEFAULT current_timestamp():::TIMESTAMP, | CONSTRAINT ttl_test_pkey PRIMARY KEY (id ASC) | ) (1 row) ``` ### Set localities `RESTORE` on [`REGIONAL BY TABLE`](#regional-by-table), [`REGIONAL BY ROW`](#regional-by-row), and [`GLOBAL`](#global) tables is supported with some limitations — see Restoring to multi-region databases for more detail. #### Set the table locality to `REGIONAL BY TABLE` To optimize read and write access to the data in a table from the primary region, use the following statement, which sets the table's home region to the primary region: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE {table} SET LOCALITY REGIONAL BY TABLE IN PRIMARY REGION; ``` To optimize read and write access to the data in a table from the `us-east-1` region, use the following statement, which sets the table's home region to `us-east-1`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE {table} SET LOCALITY REGIONAL BY TABLE IN "us-east-1"; ``` If no region is supplied, `REGIONAL BY TABLE` defaults the table's home region to the primary region. For more information about how this table locality works, see Regional tables. #### Set the table locality to `REGIONAL BY ROW` Before setting the locality to `REGIONAL BY ROW` on a table targeted by a changefeed, read the considerations in Changefeeds on regional by row tables. To make an existing table a *regional by row* table, use the following statement: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE {table} SET LOCALITY REGIONAL BY ROW; ``` Every row in a regional by row table has a column of type `crdb_internal_region` that represents the row's home region. By default, this column is called `crdb_region` and is hidden. To see a row's home region, issue a statement like the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT crdb_region, id FROM {table}; ``` To update an existing row's home region, use an `UPDATE` statement like the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} UPDATE {table} SET crdb_region = 'eu-west' WHERE id IN (...) ``` To add a new row to a regional by row table, you must choose one of the following options. * Let CockroachDB set the row's home region automatically. It will use the region of the gateway node from which the row is inserted. * Set the home region explicitly using an `INSERT` statement like the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} INSERT INTO {table} (crdb_region, ...) VALUES ('us-east-1', ...); ``` This is necessary because every row in a regional by row table must have a home region. If you do not set a home region for a row in a regional by row table, it defaults to the value returned by the built-in function `gateway_region()`. If the value returned by `gateway_region()` does not belong to the multi-region database the table is a part of, the home region defaults to the database's primary region. For more information about how this table locality works, see Regional by row tables. Note that you can use a name other than `crdb_region` for the hidden column by using the following statements: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE foo SET LOCALITY REGIONAL BY ROW AS bar; SELECT bar, id FROM foo; INSERT INTO foo (bar, ...) VALUES ('us-east-1', ...); ``` In fact, you can specify any column definition you like for the `REGIONAL BY ROW AS` column, as long as the column is of type `crdb_internal_region` and is not nullable. For example, you could modify the movr schema to have a region column generated as: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE rides ADD COLUMN region crdb_internal_region AS ( CASE WHEN city IN ('new york', 'boston', 'washington dc', 'chicago', 'detroit', 'minneapolis') THEN 'us-east-1' WHEN city IN ('san francisco', 'seattle', 'los angeles') THEN 'us-west-1' WHEN city IN ('amsterdam', 'paris', 'rome') THEN 'eu-west-1' END ) STORED; ``` Note that the SQL engine will avoid sending requests to nodes in other regions when it can instead read a value from a unique column that is stored locally. This capability is known as *locality optimized search*. #### Turn on auto-rehoming for `REGIONAL BY ROW` tables **This feature is in preview** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com). This feature is disabled by default. When auto-rehoming is enabled, the [home regions](#crdb_region) of rows in [`REGIONAL BY ROW`](#set-the-table-locality-to-regional-by-row) tables are automatically set to the gateway region of any `UPDATE` or `UPSERT` statements that write to those rows. This functionality is provided by adding an `ON UPDATE` expression to the [`crdb_region`](#crdb_region) column of newly created regional by row tables. To enable auto-rehoming using the session setting, issue the following statement: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET enable_auto_rehoming = on; ``` Once enabled, the auto-rehoming behavior described here has the following limitations: * It **will only apply to newly created `REGIONAL BY ROW` tables**, using an `ON UPDATE` expression that is added to the [`crdb_region`](#crdb_region) column. Existing `REGIONAL BY ROW` tables will not be auto-rehomed. * The [`crdb_region`](#crdb_region) column from a [`REGIONAL BY ROW`](#set-the-table-locality-to-regional-by-row) table cannot be referenced as a foreign key from another table. To enable auto-rehoming for an existing `REGIONAL BY ROW` table, manually update it using an [`ALTER TABLE... ALTER COLUMN`](#alter-column) statement with an `ON UPDATE` expression: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE {table} ALTER COLUMN crdb_region SET ON UPDATE rehome_row()::db.public.crdb_internal_region; ``` ##### Example 1. Follow steps 1 and 2 from the Low Latency Reads and Writes in a Multi-Region Cluster tutorial. This will involve starting a `cockroach demo` cluster in a terminal window (call it *terminal 1*). 2. From the SQL client running in terminal 1, set the setting that enables auto-rehoming. You must issue this setting before creating the `REGIONAL BY ROW` tables that you want auto-rehomed. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET enable_auto_rehoming = on; ``` 3. In a second terminal window (call it *terminal 2*), finish the tutorial starting from step 3 onward to finish loading the cluster with data and applying the multi-region SQL configuration. 4. Switch back to terminal 1, and check the gateway region of the node you are currently connected to: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT gateway_region(); ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} gateway_region ------------------ us-east1 (1 row) ``` 5. Open another terminal (call it *terminal 3*), and use `cockroach sql` to connect to a node in a different region in the demo cluster: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} cockroach sql --insecure --host localhost --port 26262 ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} # Welcome to the CockroachDB SQL shell. # All statements must be terminated by a semicolon. # To exit, type: \q. # # Server version: CockroachDB CCL v23.2.29 (x86_64-apple-darwin19, built 2026-02-19 00:00:00) (same version as client) # Cluster ID: 87b22d9b-b9ce-4f3a-8635-acad89c5981f # Organization: Cockroach Demo # # Enter \? for a brief introduction. # root@localhost:26262/defaultdb> ``` 6. From the SQL shell prompt that appears in terminal 3, switch to the `movr` database, and verify that the current gateway node is in a different region (`us-west1`): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} USE movr; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT gateway_region(); ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} gateway_region ------------------ us-west1 (1 row) ``` 7. Still in terminal 3, update a row in the `vehicles` table that is homed in the `us-east1` region. After the update, it should be homed in the current gateway node's home region, `us-west1`. 1. First, pick a row at random from the `us-east1` region: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} select * from vehicles where region = 'us-east1' limit 1; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | city | type | owner_id | creation_time | status | current_location | ext | region ---------------------------------------+--------+------+--------------------------------------+----------------------------+-----------+------------------------------+--------------------------------------------+----------- 3e127e68-a3f9-487d-aa56-bf705beca05a | boston | bike | 2f057d6b-ba8d-4f56-8fd9-894b7c082713 | 2021-10-28 16:19:22.309834 | available | 039 Stacey Plain | {"brand": "FujiCervelo", "color": "green"} | us-east1 | | | | | | Lake Brittanymouth, LA 09374 | | (1 row) ``` 2. Next, update that row's `city` and `current_location` to addresses in Seattle, WA (USA). Note that this UUID is different than what you will see in your cluster, so you'll have to update the query accordingly. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} UPDATE vehicles set (city, current_location) = ('seattle', '2604 1st Ave, Seattle, WA 98121-1305') WHERE id = '3e127e68-a3f9-487d-aa56-bf705beca05a'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} UPDATE 1 ``` 3. Finally, verify that the row has been auto-rehomed in this gateway's region by running the following statement and checking that the `region` column is now `us-west1`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT * FROM vehicles WHERE id = '3e127e68-a3f9-487d-aa56-bf705beca05a'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | city | type | owner_id | creation_time | status | current_location | ext | region ---------------------------------------+---------+------+--------------------------------------+----------------------------+-----------+--------------------------------------+--------------------------------------------+----------- 3e127e68-a3f9-487d-aa56-bf705beca05a | seattle | bike | 2f057d6b-ba8d-4f56-8fd9-894b7c082713 | 2021-10-28 16:19:22.309834 | available | 2604 1st Ave, Seattle, WA 98121-1305 | {"brand": "FujiCervelo", "color": "green"} | us-west1 (1 row) ``` #### Set the table locality to `GLOBAL` To optimize read access to the data in a table from any region (that is, globally), use the following statement: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE {table} SET LOCALITY GLOBAL; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE SET LOCALITY ``` For more information about how this table locality works, see Global tables. ### Set table schema #### Setup To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo ``` #### Change the schema of a table Suppose you want to add the `promo_codes` table to a new schema called `cockroach_labs`. By default, unqualified tables created in the database belong to the `public` schema: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} schema_name | table_name | type | estimated_row_count --------------+----------------------------+-------+---------------------- public | promo_codes | table | 1000 public | rides | table | 500 public | user_promo_codes | table | 0 public | users | table | 50 public | vehicle_location_histories | table | 1000 public | vehicles | table | 15 (6 rows) ``` If the new schema does not already exist, create it: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE SCHEMA IF NOT EXISTS cockroach_labs; ``` Then, change the table's schema: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE promo_codes SET SCHEMA cockroach_labs; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} schema_name | table_name | type | estimated_row_count -----------------+----------------------------+-------+---------------------- cockroach_labs | promo_codes | table | 1000 public | rides | table | 500 public | user_promo_codes | table | 0 public | users | table | 50 public | vehicle_location_histories | table | 1000 public | vehicles | table | 15 (6 rows) ``` ### Scatter tables Before scattering, you can view the current replica and leaseholder distribution for a table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} WITH range_details AS (SHOW RANGES FROM TABLE movr.users WITH DETAILS) SELECT range_id, lease_holder, replicas from range_details; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} range_id | lease_holder | replicas -----------+--------------+----------- 94 | 2 | {2,5,9} 78 | 3 | {3,5,9} 77 | 2 | {2,4,9} 76 | 3 | {3,6,9} 95 | 3 | {3,5,9} 75 | 2 | {2,5,8} 87 | 4 | {2,4,7} 85 | 2 | {2,5,9} 86 | 7 | {3,4,7} (9 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE movr.users SCATTER; ``` After scattering, recheck the leaseholder distribution: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} WITH range_details AS (SHOW RANGES FROM TABLE movr.users WITH DETAILS) SELECT range_id, lease_holder, replicas from range_details; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} range_id | lease_holder | replicas -----------+--------------+----------- 94 | 5 | {2,5,8} 78 | 1 | {1,5,9} 77 | 1 | {1,4,9} 76 | 1 | {1,6,9} 95 | 1 | {1,5,9} 75 | 1 | {1,5,8} 87 | 7 | {2,4,7} 85 | 1 | {1,5,9} 86 | 3 | {3,4,7} (9 rows) ``` ### Split and unsplit tables #### Setup The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App. To follow along, run `cockroach demo` with the `--geo-partitioned-replicas` flag. This command opens an interactive SQL shell to a temporary, 9-node in-memory cluster with the `movr` database. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo --geo-partitioned-replicas ``` #### Split a table ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW RANGES FROM TABLE users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} start_key | end_key | range_id | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities -----------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------------+--------------+--------------------------+----------+----------------------------------------------------------------------------- NULL | /"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 37 | 0.000116 | 1 | region=us-east1,az=b | {1,6,8} | {"region=us-east1,az=b","region=us-west1,az=c","region=europe-west1,az=c"} /"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | /"boston"/"333333D\x00\x80\x00\x00\x00\x00\x00\x00\n" | 46 | 0.000886 | 8 | region=europe-west1,az=c | {1,6,8} | {"region=us-east1,az=b","region=us-west1,az=c","region=europe-west1,az=c"} /"boston"/"333333D\x00\x80\x00\x00\x00\x00\x00\x00\n" | /"los angeles"/"\x99\x99\x99\x99\x99\x99H\x00\x80\x00\x00\x00\x00\x00\x00\x1e" | 45 | 0.00046 | 8 | region=europe-west1,az=c | {2,4,8} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=c"} /"los angeles"/"\x99\x99\x99\x99\x99\x99H\x00\x80\x00\x00\x00\x00\x00\x00\x1e" | /"new york"/"\x19\x99\x99\x99\x99\x99J\x00\x80\x00\x00\x00\x00\x00\x00\x05" | 44 | 0.001015 | 8 | region=europe-west1,az=c | {1,6,8} | {"region=us-east1,az=b","region=us-west1,az=c","region=europe-west1,az=c"} /"new york"/"\x19\x99\x99\x99\x99\x99J\x00\x80\x00\x00\x00\x00\x00\x00\x05" | /"paris"/"\xcc\xcc\xcc\xcc\xcc\xcc@\x00\x80\x00\x00\x00\x00\x00\x00(" | 77 | 0.000214 | 8 | region=europe-west1,az=c | {1,6,8} | {"region=us-east1,az=b","region=us-west1,az=c","region=europe-west1,az=c"} /"paris"/"\xcc\xcc\xcc\xcc\xcc\xcc@\x00\x80\x00\x00\x00\x00\x00\x00(" | /"san francisco"/"\x80\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x19" | 43 | 0.001299 | 8 | region=europe-west1,az=c | {3,4,8} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=c"} /"san francisco"/"\x80\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x19" | /"seattle"/"ffffffH\x00\x80\x00\x00\x00\x00\x00\x00\x14" | 61 | 0.000669 | 3 | region=us-east1,az=d | {3,4,8} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=c"} /"seattle"/"ffffffH\x00\x80\x00\x00\x00\x00\x00\x00\x14" | /"washington dc"/"L\xcc\xcc\xcc\xcc\xccL\x00\x80\x00\x00\x00\x00\x00\x00\x0f" | 57 | 0.000671 | 3 | region=us-east1,az=d | {3,4,7} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=b"} /"washington dc"/"L\xcc\xcc\xcc\xcc\xccL\x00\x80\x00\x00\x00\x00\x00\x00\x0f" | NULL | 87 | 0.000231 | 4 | region=us-west1,az=a | {3,4,7} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=b"} (9 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users SPLIT AT VALUES ('chicago'), ('new york'), ('seattle'); ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} key | pretty | split_enforced_until -------------------------------+-------------+-------------------------------------- \275\211\022chicago\000\001 | /"chicago" | 2262-04-11 23:47:16.854776+00:00:00 \275\211\022new york\000\001 | /"new york" | 2262-04-11 23:47:16.854776+00:00:00 \275\211\022seattle\000\001 | /"seattle" | 2262-04-11 23:47:16.854776+00:00:00 (3 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW RANGES FROM TABLE users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} start_key | end_key | range_id | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities -----------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------- NULL | /"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 37 | 0.000116 | 1 | region=us-east1,az=b | {1,6,8} | {"region=us-east1,az=b","region=us-west1,az=c","region=europe-west1,az=c"} /"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | /"amsterdam"/PrefixEnd | 46 | 0.000446 | 8 | region=europe-west1,az=c | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} /"amsterdam"/PrefixEnd | /"boston" | 70 | 0 | 8 | region=europe-west1,az=c | {3,6,8} | {"region=us-east1,az=d","region=us-west1,az=c","region=europe-west1,az=c"} /"boston" | /"boston"/"333333D\x00\x80\x00\x00\x00\x00\x00\x00\n" | 71 | 0.00044 | 1 | region=us-east1,az=b | {1,6,8} | {"region=us-east1,az=b","region=us-west1,az=c","region=europe-west1,az=c"} /"boston"/"333333D\x00\x80\x00\x00\x00\x00\x00\x00\n" | /"boston"/PrefixEnd | 45 | 0.000225 | 2 | region=us-east1,az=c | {2,3,8} | {"region=us-east1,az=c","region=us-east1,az=d","region=europe-west1,az=c"} /"boston"/PrefixEnd | /"chicago" | 72 | 0 | 8 | region=europe-west1,az=c | {2,4,8} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=c"} /"chicago" | /"los angeles" | 74 | 0 | 8 | region=europe-west1,az=c | {2,4,8} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=c"} /"los angeles" | /"los angeles"/"\x99\x99\x99\x99\x99\x99H\x00\x80\x00\x00\x00\x00\x00\x00\x1e" | 73 | 0.000235 | 8 | region=europe-west1,az=c | {2,4,8} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=c"} /"los angeles"/"\x99\x99\x99\x99\x99\x99H\x00\x80\x00\x00\x00\x00\x00\x00\x1e" | /"los angeles"/PrefixEnd | 44 | 0.000462 | 4 | region=us-west1,az=a | {4,6,8} | {"region=us-west1,az=a","region=us-west1,az=c","region=europe-west1,az=c"} /"los angeles"/PrefixEnd | /"new york" | 68 | 0 | 8 | region=europe-west1,az=c | {2,6,8} | {"region=us-east1,az=c","region=us-west1,az=c","region=europe-west1,az=c"} /"new york" | /"new york"/"\x19\x99\x99\x99\x99\x99J\x00\x80\x00\x00\x00\x00\x00\x00\x05" | 69 | 0.000553 | 8 | region=europe-west1,az=c | {1,3,8} | {"region=us-east1,az=b","region=us-east1,az=d","region=europe-west1,az=c"} /"new york"/"\x19\x99\x99\x99\x99\x99J\x00\x80\x00\x00\x00\x00\x00\x00\x05" | /"new york"/PrefixEnd | 77 | 0.000111 | 1 | region=us-east1,az=b | {1,6,8} | {"region=us-east1,az=b","region=us-west1,az=c","region=europe-west1,az=c"} /"new york"/PrefixEnd | /"paris" | 62 | 0 | 8 | region=europe-west1,az=c | {3,4,8} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=c"} /"paris" | /"paris"/"\xcc\xcc\xcc\xcc\xcc\xcc@\x00\x80\x00\x00\x00\x00\x00\x00(" | 63 | 0.000103 | 8 | region=europe-west1,az=c | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} /"paris"/"\xcc\xcc\xcc\xcc\xcc\xcc@\x00\x80\x00\x00\x00\x00\x00\x00(" | /"paris"/PrefixEnd | 43 | 0.000525 | 8 | region=europe-west1,az=c | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} /"paris"/PrefixEnd | /"rome" | 64 | 0 | 8 | region=europe-west1,az=c | {3,4,8} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=c"} /"rome" | /"rome"/PrefixEnd | 65 | 0.000539 | 8 | region=europe-west1,az=c | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} /"rome"/PrefixEnd | /"san francisco" | 66 | 0 | 8 | region=europe-west1,az=c | {3,4,8} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=c"} /"san francisco" | /"san francisco"/"\x80\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x19" | 67 | 0.000235 | 4 | region=us-west1,az=a | {4,5,8} | {"region=us-west1,az=a","region=us-west1,az=b","region=europe-west1,az=c"} /"san francisco"/"\x80\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x19" | /"san francisco"/PrefixEnd | 61 | 0.000365 | 4 | region=us-west1,az=a | {4,5,6} | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"} /"san francisco"/PrefixEnd | /"seattle" | 88 | 0 | 3 | region=us-east1,az=d | {3,4,8} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=c"} /"seattle" | /"seattle"/"ffffffH\x00\x80\x00\x00\x00\x00\x00\x00\x14" | 89 | 0.000304 | 3 | region=us-east1,az=d | {3,4,8} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=c"} /"seattle"/"ffffffH\x00\x80\x00\x00\x00\x00\x00\x00\x14" | /"seattle"/PrefixEnd | 57 | 0.000327 | 3 | region=us-east1,az=d | {3,4,7} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=b"} /"seattle"/PrefixEnd | /"washington dc" | 90 | 0 | 3 | region=us-east1,az=d | {3,4,7} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=b"} /"washington dc" | /"washington dc"/"L\xcc\xcc\xcc\xcc\xccL\x00\x80\x00\x00\x00\x00\x00\x00\x0f" | 91 | 0.000344 | 3 | region=us-east1,az=d | {1,2,3} | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"} /"washington dc"/"L\xcc\xcc\xcc\xcc\xccL\x00\x80\x00\x00\x00\x00\x00\x00\x0f" | /"washington dc"/PrefixEnd | 87 | 0.000231 | 3 | region=us-east1,az=d | {1,2,3} | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"} /"washington dc"/PrefixEnd | NULL | 157 | 0 | 4 | region=us-west1,az=a | {3,4,7} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=b"} (27 rows) ``` #### Split a table with a compound primary key You may want to split a table with a compound primary key. Suppose that you want MovR to offer ride-sharing services, in addition to vehicle-sharing services. Some users need to sign up to be drivers, so you need a `drivers` table to store driver information. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE drivers ( id UUID DEFAULT gen_random_uuid(), city STRING, name STRING, dl STRING DEFAULT left(md5(random()::text),8) UNIQUE CHECK (LENGTH(dl) < 9), address STRING, CONSTRAINT "primary" PRIMARY KEY (city ASC, dl ASC) ); ``` The table's compound primary key is on the `city` and `dl` columns. Note that the table automatically generates an `id` and a `dl` value using supported SQL functions if they are not provided. Because this table has several columns in common with the `users` table, you can populate the table with values from the `users` table with an `INSERT` statement: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > INSERT INTO drivers (id, city, name, address) SELECT id, city, name, address FROM users; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW RANGES FROM TABLE drivers; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} start_key | end_key | range_id | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities ------------+---------+----------+---------------+--------------+--------------------------+----------+----------------------------------------------------------------------------- NULL | NULL | 310 | 0.007218 | 7 | region=europe-west1,az=b | {1,4,7} | {"region=us-east1,az=b","region=us-west1,az=a","region=europe-west1,az=b"} (1 row) ``` Now you can split the table based on the compound primary key. Note that you do not have to specify the entire value for the primary key, just the prefix. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE drivers SPLIT AT VALUES ('new york', '3'), ('new york', '7'), ('chicago', '3'), ('chicago', '7'), ('seattle', '3'), ('seattle', '7'); ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} key | pretty | split_enforced_until --------------------------------------------+-----------------+-------------------------------------- \303\211\022new york\000\001\0223\000\001 | /"new york"/"3" | 2262-04-11 23:47:16.854776+00:00:00 \303\211\022new york\000\001\0227\000\001 | /"new york"/"7" | 2262-04-11 23:47:16.854776+00:00:00 \303\211\022chicago\000\001\0223\000\001 | /"chicago"/"3" | 2262-04-11 23:47:16.854776+00:00:00 \303\211\022chicago\000\001\0227\000\001 | /"chicago"/"7" | 2262-04-11 23:47:16.854776+00:00:00 \303\211\022seattle\000\001\0223\000\001 | /"seattle"/"3" | 2262-04-11 23:47:16.854776+00:00:00 \303\211\022seattle\000\001\0227\000\001 | /"seattle"/"7" | 2262-04-11 23:47:16.854776+00:00:00 (6 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW RANGES FROM TABLE drivers; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} start_key | end_key | range_id | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities ------------------+-----------------+----------+---------------+--------------+--------------------------+----------+----------------------------------------------------------------------------- NULL | /"chicago"/"3" | 310 | 0.001117 | 7 | region=europe-west1,az=b | {3,4,7} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=b"} /"chicago"/"3" | /"chicago"/"7" | 314 | 0 | 7 | region=europe-west1,az=b | {3,4,7} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=b"} /"chicago"/"7" | /"new york"/"3" | 315 | 0.000933 | 7 | region=europe-west1,az=b | {3,4,7} | {"region=us-east1,az=d","region=us-west1,az=a","region=europe-west1,az=b"} /"new york"/"3" | /"new york"/"7" | 311 | 0 | 7 | region=europe-west1,az=b | {1,4,7} | {"region=us-east1,az=b","region=us-west1,az=a","region=europe-west1,az=b"} /"new york"/"7" | /"seattle"/"3" | 312 | 0.001905 | 7 | region=europe-west1,az=b | {1,4,7} | {"region=us-east1,az=b","region=us-west1,az=a","region=europe-west1,az=b"} /"seattle"/"3" | /"seattle"/"7" | 316 | 0.000193 | 7 | region=europe-west1,az=b | {1,6,7} | {"region=us-east1,az=b","region=us-west1,az=c","region=europe-west1,az=b"} /"seattle"/"7" | NULL | 317 | 0.00307 | 7 | region=europe-west1,az=b | {1,4,7} | {"region=us-east1,az=b","region=us-west1,az=a","region=europe-west1,az=b"} (7 rows) ``` #### Set the expiration on a split enforcement You can specify the time at which a split enforcement expires by adding a `WITH EXPIRATION` clause to your `SPLIT` statement. Supported expiration values include `DECIMAL`, `INTERVAL`, `TIMESTAMP`, and `TIMESTAMPZ`. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE vehicles SPLIT AT VALUES ('chicago'), ('new york'), ('seattle') WITH EXPIRATION '2022-01-10 23:30:00+00:00'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} key | pretty | split_enforced_until -------------------------------+-------------+------------------------------- \276\211\022chicago\000\001 | /"chicago" | 2022-01-10 23:30:00+00:00:00 \276\211\022new york\000\001 | /"new york" | 2022-01-10 23:30:00+00:00:00 \276\211\022seattle\000\001 | /"seattle" | 2022-01-10 23:30:00+00:00:00 (3 rows) ``` You can see the split's expiration date in the `split_enforced_until` column. The `crdb_internal.ranges` table also contains information about ranges in your CockroachDB cluster, including the `split_enforced_until` column. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT range_id, start_pretty, end_pretty, split_enforced_until FROM crdb_internal.ranges WHERE table_name='vehicles'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} range_id | start_pretty | end_pretty | split_enforced_until -----------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+-------------------------------------- 38 | /Table/54 | /Table/54/1/"amsterdam" | NULL 55 | /Table/54/1/"amsterdam" | /Table/54/1/"amsterdam"/PrefixEnd | NULL 109 | /Table/54/1/"amsterdam"/PrefixEnd | /Table/54/1/"boston" | NULL 114 | /Table/54/1/"boston" | /Table/54/1/"boston"/"\"\"\"\"\"\"B\x00\x80\x00\x00\x00\x00\x00\x00\x02" | NULL 50 | /Table/54/1/"boston"/"\"\"\"\"\"\"B\x00\x80\x00\x00\x00\x00\x00\x00\x02" | /Table/54/1/"boston"/"333333D\x00\x80\x00\x00\x00\x00\x00\x00\x03" | 2262-04-11 23:47:16.854776+00:00:00 49 | /Table/54/1/"boston"/"333333D\x00\x80\x00\x00\x00\x00\x00\x00\x03" | /Table/54/1/"boston"/PrefixEnd | 2262-04-11 23:47:16.854776+00:00:00 129 | /Table/54/1/"boston"/PrefixEnd | /Table/54/1/"chicago" | NULL 241 | /Table/54/1/"chicago" | /Table/54/1/"los angeles" | 2022-01-10 23:30:00+00:00:00 130 | /Table/54/1/"los angeles" | /Table/54/1/"los angeles"/PrefixEnd | NULL 131 | /Table/54/1/"los angeles"/PrefixEnd | /Table/54/1/"new york" | NULL 132 | /Table/54/1/"new york" | /Table/54/1/"new york"/"\x11\x11\x11\x11\x11\x11A\x00\x80\x00\x00\x00\x00\x00\x00\x01" | 2022-01-10 23:30:00+00:00:00 48 | /Table/54/1/"new york"/"\x11\x11\x11\x11\x11\x11A\x00\x80\x00\x00\x00\x00\x00\x00\x01" | /Table/54/1/"new york"/PrefixEnd | 2262-04-11 23:47:16.854776+00:00:00 ... (46 rows) ``` #### Unsplit a table Create a `drivers` table and split the table based on the compound primary key as described in [Split a table with a compound primary key](#split-a-table-with-a-compound-primary-key). To remove the split enforcements, run the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE drivers UNSPLIT AT VALUES ('new york', '3'), ('new york', '7'), ('chicago', '3'), ('chicago', '7'), ('seattle', '3'), ('seattle', '7'); ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} key | pretty --------------------------------------------+----------------------------- \xc3\x89\x12new york\x00\x01\x123\x00\x01 | /Table/59/1/"new york"/"3" \xc3\x89\x12new york\x00\x01\x127\x00\x01 | /Table/59/1/"new york"/"7" \xc3\x89\x12chicago\x00\x01\x123\x00\x01 | /Table/59/1/"chicago"/"3" \xc3\x89\x12chicago\x00\x01\x127\x00\x01 | /Table/59/1/"chicago"/"7" \xc3\x89\x12seattle\x00\x01\x123\x00\x01 | /Table/59/1/"seattle"/"3" \xc3\x89\x12seattle\x00\x01\x127\x00\x01 | /Table/59/1/"seattle"/"7" (6 rows) ``` You can see the split's expiration date in the `split_enforced_until` column. The `crdb_internal.ranges` table also contains information about ranges in your CockroachDB cluster, including the `split_enforced_until` column. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT range_id, start_pretty, end_pretty, split_enforced_until FROM crdb_internal.ranges WHERE table_name='drivers'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} range_id | start_pretty | end_pretty | split_enforced_until -----------+----------------------------+----------------------------+----------------------- 74 | /Table/59 | /Table/59/1/"chicago"/"3" | NULL 77 | /Table/59/1/"chicago"/"3" | /Table/59/1/"chicago"/"7" | NULL 78 | /Table/59/1/"chicago"/"7" | /Table/59/1/"new york"/"3" | NULL 75 | /Table/59/1/"new york"/"3" | /Table/59/1/"new york"/"7" | NULL 76 | /Table/59/1/"new york"/"7" | /Table/59/1/"seattle"/"3" | NULL 79 | /Table/59/1/"seattle"/"3" | /Table/59/1/"seattle"/"7" | NULL 80 | /Table/59/1/"seattle"/"7" | /Max | NULL (7 rows) ``` The `drivers` table is still split into ranges at specific primary key column values, but the `split_enforced_until` column is now `NULL` for all ranges in the table. The split is no longer enforced, and CockroachDB can merge the data in the table as needed. ### Validate constraints #### Setup To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo ``` #### Validate a constraint In the example [Add the foreign key constraint with `CASCADE`](#add-the-foreign-key-constraint-with-cascade), we add a foreign key constraint as follows: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE vehicles ADD CONSTRAINT users_fk FOREIGN KEY (city, owner_id) REFERENCES users (city, id) ON DELETE CASCADE; ``` To ensure that the data added to the `vehicles` table prior to the creation of the `users_fk` constraint conforms to that constraint, run the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE vehicles VALIDATE CONSTRAINT users_fk; ``` If present in a `CREATE TABLE` statement, the table is considered validated because an empty table trivially meets its constraints. ## See also * Multi-Region Capabilities Overview * Online Schema Changes * Constraints * Foreign Key Constraint * Replication Controls * SQL Audit Logging * `CREATE TABLE` * `CREATE INDEX` * `ALTER INDEX` * `ALTER PARTITION` * `SHOW JOBS` * `BACKUP` * `RESTORE` * `WITH` (storage parameter) * Selection Queries * Distribution Layer * Replication Layer * Online Schema Changes * SQL Statements