Strange FK relationship - how, and is it right?

[Ramesh]

Hello all,

I have a scenario in which there are three tables, A, B and C. A and B
have PK columns, both are of the same type. C has a FK column, of the
same type as the PK columns of A and B, but the values in it should be
either in the PK column of A or in the PK column of B...

I figured out I could do this by creating a check constraint along with
a user defined function to make sure the master table (either A or B)
has a corresponding row before a row is inserted or updated into C. But
I don't know how to handle deletions from A and B: how to make sure the
corresponding row in C is deleted first. Any help would be greatly
appreciated. Thanks.

Now the more important question: C has 6 columns, and all these columns
are common to both A and B. That is, the common columns of A and B have
been moved to a new table C. Apart from this, there is no purpose or
meaning to having the table C; if it were not for C, A and B would've
had 6 additional columns. I know such factoring is common in the OO
world, but is it right in the RDBMS world? What else should've been
done? Any thoughts/suggestions? Thanks a lot in advance.

- Ramesh

[Alexander Kuznetsov]

i'd do it the other way around:

create table c(c int primary key,
c_type char(1) not null check(c_type in ('A', 'B')),
unique (c, c_type))
go
create table a(
c int not null,
c_type char(1) not null check(c_type = 'A'),
foreign key(c, c_type) references c(c,c_type))
go
create table b(
c int not null,
c_type char(1) not null check(c_type = 'B'),
foreign key(c, c_type) references c(c,c_type))
go
drop table a
drop table b
drop table c