Document that constraints are now eagerly validated

[SpicyThrow]

Re: ADD CONSTRAINT

Issue Description

In the notes section of the add foreign key constraint, it should be noted that the database validates the foreign key constraint when it's added. There's no sign that this is happening when one does this and it's good to know the validation is happening.

Suggested Resolution

[jseldess]

Thanks, @SpicyThrow. There might be other places where we should explain that we eagerly validate existing data at the time a constraint is applied as well. The exception is if the constraint is added with the NOT VALID clause. In that case, the user must use the VALIDATE CONSTRAINT statement to check.

We should update the VALIDATE CONSTRAINT docs to also mention that we eagerly validate constraints but that the statement is still useful when a constraint is added with NOT VALID.

[jseldess]

Looks like NOT VALID still needs to be documented as well: #5187.

[ajwerner]

I stumbled into this recently. It's definitely confusing. I'd start by changing a few words in the following example and having it create the constraint NOT VALID.

https://www.cockroachlabs.com/docs/v21.1/validate-constraint#examples

[jseldess]

Thanks, @ajwerner. This is a known gap. I started a PR a long, long time ago and then let that go state. @ericharmeling is on it now. Closing in favor of #5187.