Re: Composite UNIQUE across two tables?

Hi Dirk,

On 3/11/08, Dirk Jagdmann <jagdmann(at)gmail(dot)com> wrote:
> I vote against duplicating site_group_id in the users table and the
> proposed unique constraint with a function. Because all those might
> fail, if you ever want to change the relationship between a site and a
> site group.

Good point!

> My advise would be to have two triggers for insert/update on the site
> and users table that check the uniqueness of the username with the
> site_group. A have made some tests with inserts and updates on the
> existing users and sites and these two functions seem to work.

I think this is the way that I'll go. I'd hoped to somehow express this
solely in the design, if you know what i mean (e.g. without writing
SPs), but it looks like this is the best way to do it.

> One remark about your schema: If you use PostgreSQL, use the "text"
> datatype for strings, since you don't limit yourself with the string
> length.

[snip]

For some reason I assumed varchar had an advantage over text,
but a quick check of the docs suggests that's not the case. Thanks
for this tip! :-)

Thank you for your taking the time to write this up, it's very much
appreciated.

Cheers,
J.