Is it possible to have a constraint that goes across columns in different tables...for example, if you have a UNIQUE constraint, if you add something to column A in table A, you can't add that same value to column A in table A or table B.

in a word: no

in other database systems you might be able to declare a check constraint or even an assertion but mysql does not support these