The database design is a bit odd.
Odd database designs will result in odd SQL assertions.
Odd can be anything from bad-performing, to ugly-serialization, to unreadable/difficult to specify.
Let's call your table T1:
SYNONYM_NAME TABLE_NAME
==========================
A A_A
B B_A
C C_A
D D_A
So the constraint is as Chris proposed the postfix of column values in TABLE_NAME should always be the same.
Why store that postfix in a redundant manner then?
Let's add table T2:
T2
POSTFIX
========
A
And constraint T2 to only have a single row in it:
create assertion ... as check((select count(*) from T2) = 1)
Then modify the contents of T1 as follows:
SYNONYM_NAME TABLE_NAME
==========================
A A
B B
C C
D D
And finally create a view to mimic you orignal output:
create or replace view ... as
select t1.synonym_name
,t1.table_name||'_'||t2.postfix as table_name
from T1 t1
,T2 t2Now the question that begs to be asked: I suspect there also is another constraint on your original T1?
CHECK(SYNONYM_NAME = SUBSTR(TABLE_NAME,1,instr(table_name,'_')-1))
?
In that case just get rid of the T1.TABLE_NAME column all together and have your view do this:
create or replace view ... as
select t1.synonym_name
,t1.synonym_name||'_'||t2.postfix as table_name
from T1 t1
,T2 t2Anyhow, if you really want to stick to your odd database design, I'd word the assertion as follows:
check
(not exists
(select 'x'
from T1 t1
where exists
(select 'y'
from T1 t2
where t1.synonym_name <> t2.synonym_name
and substr(t1.table_name,-1,1) <> substr(t2.table_name,-1,1)))
)
Evaluation of this expression will stop on encounter of the first 'mismatch', instead of continue scanning to compute the count(*).
But still modifying your design will end up being the better solution: performance-wise, serialization-wise, maintainability-wise.