cockroachdb / cockroachdb/docs

Feedback: ALTER TABLE - wrong primary constraint name in example

Open
#19,543 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
HTML
Stars
212
Forks
476
Avg merge
40m
Merged PRs (30d)
3

Description

Page: https://cockroachlabs.com/docs/v25.1/alter-table.html

## What is the reason for your feedback?

[ ] Missing the information I need

[ ] Too complicated

[X] Out of date

[ ] Something is broken

[ ] Other

## Additional details

The example of how to change the primary key using DROP CONSTRAINT and ADD CONSTRAINT has an error. It says to use the primary key constraint named "primary" but this produces an error.

For example, using the example from the doc page

```
demo@127.0.0.1:26257/sth> 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)
-> );
CREATE TABLE

Time: 21ms total (execution 20ms / network 1ms)

demo@127.0.0.1:26257/sth> show create table users;
table_name | create_statement
-------------+-----------------------------------------------------------
users | CREATE TABLE public.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)

Time: 22ms total (execution 21ms / network 0ms)

demo@127.0.0.1:26257/sth> ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE

Time: 293ms total (execution 291ms / network 2ms)

demo@127.0.0.1:26257/sth> BEGIN;
-> ALTER TABLE users DROP CONSTRAINT "primary";
-> ALTER TABLE users ADD CONSTRAINT "primary" PRIMARY KEY (city, name, id);
-> COMMIT;
BEGIN

Time: 0ms total (execution 1ms / network 0ms)

ERROR: constraint "primary" of relation "users" does not exist
SQLSTATE: 42704
ERROR: current transaction is aborted, commands ignored until end of transaction block
SQLSTATE: 25P02
ROLLBACK

Time: 0ms total (execution 0ms / network 0ms)
```

Notice above that it fails with the primary key constraint named "primary". However, it works when the correct primary key constraint name is specified, as follows:

```
demo@127.0.0.1:26257/sth> BEGIN;
-> ALTER TABLE users DROP CONSTRAINT "users_pkey";
-> ALTER TABLE users ADD CONSTRAINT "users_pkey" PRIMARY KEY (city, name, id);
-> COMMIT;
BEGIN

Time: 2ms total (execution 8ms / network 2ms)

ALTER TABLE

Time: 33ms total (execution 33ms / network 1ms)

ALTER TABLE

Time: 7ms total (execution 6ms / network 1ms)

COMMIT

Time: 673ms total (execution 673ms / network 0ms)

```

Jira issue: DOC-13382

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.