PostgreSQL: checking existence of a key in several tables

I have this table holding user comments on trouble issues:

create table related_comment (
        id       varchar(20) references trouble_noreset,
        username varchar(20) not null,
        comment  varchar(320) not null,
        created  timestamp default current_timestamp
);

and it works ok. But now after some usage a new table similar to the existing trouble_noreset has emerged - the trouble_reported.

Since both tables have an id column, but I do not want to merg开发者_开发技巧e them together, is there maybe a way to modify the constraint for the related_comment table?

From searching around I understand, that I can't have a foreign key across several tables.

But maybe I can have something like:

create table related_comment (
        id       varchar(20) check (id exists in trouble_noreset or id exists in trouble_reported),
        username varchar(20) not null,
        comment  varchar(320) not null,
        created  timestamp default current_timestamp
);

? I'm using PostgreSQL 8.4.7 with CentOS 5.5

Thank you! Alex

To make the related_comment table useful, you have to use distinct keys for the trouble_noreset and trouble_reported tables anyway, else you don't know how to join.

I would implement it like this:

create table related_comment (
        id int4 primary key,
        noreset_id       varchar(20),
        trouble_id       varchar(20),
        username varchar(20) not null,
        comment  varchar(320) not null,
        created  timestamp default current_timestamp
);

and create the two required foreign key indexes, and a check that requires that exactly one of noreset_id and trouble_id is set.

Sounds like your foreign key is backwards. I'd add a other table for comment threads (related_comment_thread), FK related_comment to related_comment_thread, then FK trouble_noreset and trouble_reported to related_comment_thread:

related_comment_thread (
    -- standard bookkeeping stuff like ids and timestamps
)
related_comment (
    -- as now but no FK for id, each comment gets its own unique id
    thread references related_comment_thread
)
trouble_noreset (
    -- what's there now
    comments references related_comment_thead
)
trouble_reported (
    -- what's there now
    comments references related_comment_thead
)

This way you get sensible referential integrity across all your tables at the cost of an extra join; relational databases are good at joins though so there's nothing to worry about. This approach also makes it trivially easy to add comments to another table if you need such a thing in the future.