sql: ALTER TABLE .. VALIDATE CONSTRAINT substatement

[jseldess]

Complete reference documentation for the ALTER TABLE .. VALIDATE CONSTRAINT statement.

Intro, Required Privileges, and Synopsis are complete. We need to complete Parameters and Examples. For style reference, see ADD COLUMN or ADD CONSTRAINT.

More reference:

• CockroachDB docs on constraints: https://www.cockroachlabs.com/docs/dev/constraints.html
• CockroachDB test of ALTER TABLE and substatements: https://github.com/cockroachdb/cockroach/blob/542e9e435f9786e45326b339fb32c97ea3ef7d72/pkg/sql/logictest/testdata/logic_test/alter_table
• PostgreSQL docs for ALTER TABLE, which covers VALIDATE CONSTRAINT: https://www.postgresql.org/docs/9.6/static/sql-altertable.html

[a-robinson]

Just pinging this to mention that confusion over the "Unvalidated" state of a new foreign key constraint came up with a user today.

[sploiselle]

@dt Have we added meaningful validation to FKs?

[dt]

what do you mean?

VALIDATE CONSTRAINT does what it claims, it is just super slow.
ALTER TABLE ADD CONSTRAINT is a little bit of a lie since it does the NOT VALID behavior by default for FKs, requiring this additional, manual step.

@jordanlewis has started on fixing that part in #13681 though.

[a-robinson]

Yeah, the validation is meaningful, but it requires the manual step of running the command that this issue is about.

[sploiselle]

Sorry––I'd misremembered a conversation with @dt.

The constraint is validated if it's present during the CREATE TABLE statement, but if it's added after the table's created, needs to be validated manually using VALIDATE CONSTRAINT? Is that right?

[dt]

as a stopgap until something like cockroachdb/cockroach#13681 lands, we could error if NOT VALID isn't passed -- that would make it no longer a lie... but would break the ORM compat for which we added the half-baked impl in the first place. :/

[a-robinson]

If it's present during the CREATE TABLE statement, then the table is considered validated because an empty table trivially meets its constraints.

If it's added after the table is created, my understanding (please correct me if I'm wrong, @dt) is that all new updates to the table have to respect the constraint, but the existing data in the table might not because it isn't validated when the constraint is added. So the table is in a state where the data in it might not meet all of the constraints on it. Running the VALIDATE CONSTRAINT command validates all the existing data and brings the table to a fully validated state.

[dt]

@a-robinson correct. in postgres / elsewhere, this is the behavior of ALTER TABLE ... ADD CONSTRAINT with the NOT VALID modifier.

[rmloveland]

Almost filed an issue for this just now. I came across the empty Parameters table and Examples section and thought we had a build error or something.

@jseldess I'm gonna snag this if you don't mind? Shout at me if you do!

I have a really rough draft of the empty sections about ready to make a PR with. I still think it will at least be better than the current state.

[jseldess]

All yours, @rmloveland!