sql: inaccurate active constraint check in legacy schema changer

The builtin `crdb_internal.is_constraint_active` calls on the following active constraint check to determine whether or not a constraint is active:
https://github.com/cockroachdb/cockroach/blob/f5cd323c1b849e50af20cfad5488d7ed387b6e47/pkg/sql/sem/builtins/builtins.go#L7614

Where the definition of active is that the constraint exists and is enforced.

In the following scenario using the legacy schema changer, we do see that the constraint is enforced (and it does exist); however, `crdb_internal.is_constraint_active` returns false (24.1 - master):
```
demo@127.0.0.1:26257/demoapp/movr> create table t(i int, j int unique);
->
-> create table t2(q int, w int unique);
->
-> BEGIN;
->
-> alter table t add constraint aa foreign key
-> (j) references public.t2 (w);
CREATE TABLE

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

CREATE TABLE

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

BEGIN

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

ALTER TABLE

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

demo@127.0.0.1:26257/demoapp/movr OPEN> SELECT
-> crdb_internal.is_constraint_active('publi
-> c.t', 'aa');
crdb_internal.is_constraint_active
--------------------------------------
f
(1 row)

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

demo@127.0.0.1:26257/demoapp/movr OPEN> insert into t values (1, 2);
ERROR: insert on table "t" violates foreign key constraint "aa"
SQLSTATE: 23503
DETAIL: Key (j)=(2) is not present in table "t2".
CONSTRAINT: aa
demo@127.0.0.1:26257/demoapp/? ERROR>
```

We saw this behavior lead to a flake in our random schema change workload here: https://github.com/cockroachdb/cockroach/pull/119653#issuecomment-1992601082.

Jira issue: CRDB-36834

Epic CRDB-61004