> Unfortunately, that'll require locking each table and scanning it to make sure that the CHECK constraint isn't violated.
Actually, CHECK constraints can be added with the NOT VALID clause.
New tuples will be checked immediately, while the validation of existing tuples can be done later using ALTER TABLE ... VALIDATE CONSTRAINT ... which takes a less invasive lock than if you'd omitted NOT VALID.
Steve.