search

cockroachdb / docs

CockroachDB user documentation
https://cockroachlabs.com/docs
Creative Commons Attribution 4.0 International
187 stars 453 forks source link

Update ALTER TABLE docs #18153

Open annrpom opened 9 months ago

annrpom commented 9 months ago

A community issue came in with an observation about ALTER TABLE ... ADD COLUMN (#116301); they expect the following examples to behave the same way:

Ex 1

ALTER TABLE tab 
  ADD COLUMN col BOOL NOT NULL DEFAULT true;
ALTER TABLE tab
  ALTER COLUMN col DROP DEFAULT, 
  ALTER COLUMN col DROP NOT NULL;

Ex 2

ALTER TABLE tab 
  ADD COLUMN col BOOL NOT NULL DEFAULT true, 
  ALTER COLUMN col DROP DEFAULT, 
  ALTER COLUMN col DROP NOT NULL;

The first example leads to a resulting table with "true" still in column col; the second essentially cancels the NOT NULL DEFAULT true constraint - resulting in a table with NULLs in column col.

This is in fact a valid behavior and not a bug, but can be unexpected to some users; so, it might be worth documenting.

kf6nux commented 9 months ago

I commented on the other issue prior to seeing this one as open. copy/pasted below

If documenting the footgun is the desired path, I'm guessing the place to document it would be in the subcommands section of Alter Table. Perhaps a WARNING section above the existing TIP section along the lines of:

WARNING: If multiple subcommands conflict, the db may either reject the entire statement or use the final resulting schema as the only schema change to be processed.