> I have two tables, town and townalias, the latter containing alternative
> town names.
> I would like to ensure that a town name is unique per
> country-region across the two tables.
>
> Can I do this with a constraint ot do I need to implement the logic via
> trigger?
You can't have a constraint spreading multiple tables. And fixing this
using a trigger is harder than it looks, because a trigger does not see
uncommited changes made by other sessions.
So for example this will be hard to catch:
Session A: INSERT INTO town(name) VALUES ('Chicago');
Session B: INSERT INTO town(name) VALUES ('Chicago');
Session A: COMMIT;
Session B: COMMIT;
What I'd do is I'd keep the primary name in the 'townalias' table too,
maybe with a 'primary=true' flag. That way you can use traditional UNIQUE
constraint.
regards
Tomas