ADD CONSTRAINT Doc Update

[andreimatei]

Re: ADD CONSTRAINT

This page should link to the validate constraint page: https://www.cockroachlabs.com/docs/stable/validate-constraint.html

and the relationship between the two should be explained - do all constraints added after the table has been created need to be explicitly validated? Postgres seems to have a not valid clause for add constraint that defers the validation, but we don't seem to support that, do we?

[thoszhang]

To answer the above questions (I have skimmed the existing docs, but have not read them thoroughly):

do all constraints added after the table has been created need to be explicitly validated?

In 2.1, yes. In 19.1, this is still true for foreign keys, but not true for check constraints. @rmloveland has already updated the ADD CONSTRAINT docs to reflect the change for check constraints, so I'm not sure if anything further needs to be done here (we had a customer question from someone who was presumably reading the 2.1 docs).

Postgres seems to have a not valid clause for add constraint that defers the validation, but we don't seem to support that, do we?

Right, we don't yet support NOT VALID. Roughly speaking, as of 19.1, check constraints behave like the default in postgres, and foreign key constraints behave as with NOT VALID in postgres. Of course, the goal is to support both cases for both types of constraints. I filed an issue a while ago to track this: cockroachdb/cockroach#33551

[rmloveland]

Thanks for filing this Andrei, and thanks Lucy for clarifying things.

we don't yet support NOT VALID. Roughly speaking, as of 19.1, check constraints behave like the default in postgres, and foreign key constraints behave as with NOT VALID in postgres. Of course, the goal is to support both cases for both types of constraints. I filed an issue a while ago to track this: cockroachdb/cockroach#33551

It looks like cockroachdb/cockroach#33551 is now in, and as Lucy says the CHECK constraint portion is already documented.

We will be tracking the work to update the foreign key docs with the NOT VALID change via #5187. Therefore I'm closing this.