Table 1: member (id int, name char(20)), id is the primary key
Table 2: class (classid int, memberid int ), memberid is the FK to
member (id), and has unique index on memberid

Now
Table 3: Competition (classid int, result char(2))
For classid is not PK on class table, how can I make a FK constraint on
Table 3 to class(classid)?
Thanks.

If you can make class (classid) UNIQUE, you can make FK constraint from
Competition (classid) to class (classid).

shenanwei@gmail.com wrote:[color=blue]
> Table 1: member (id int, name char(20)), id is the primary key
> Table 2: class (classid int, memberid int ), memberid is the FK to
> member (id), and has unique index on memberid
>
> Now
> Table 3: Competition (classid int, result char(2))
> For classid is not PK on class table, how can I make a FK constraint on
> Table 3 to class(classid)?
> Thanks.
>[/color]

If memberid has a unique key on it then each memberid is allowed to have
a single classid. This indicates that classid is a nonrepeating
attribute of table1's id and should probably be part of table1.

This looks more like a design issue.

Given: The information you supplied.
Assumption: Table 3 has a unique key on classid.

Table 1: (id int not null, name char(20), classid int)
Table 2: ((classid int not null, result char(2))
With a foreign key on table1.classid will enforce the desired RI constraint.

If you have to keep the two initial tables as they are; then you are
missing the "class" parent table that table 2 should have a foreign key
to. In this case; table 3 would also have a foreign key to the missing
table.


Phil Sherman

Tonkuma wrote:
[color=blue]
> If you can make class (classid) UNIQUE, you can make FK constraint from
> Competition (classid) to class (classid).[/color]

And if you cannot make it unique, stick to triggers to check the consistency
on each insert/update/delete.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

To make a trigger, everytime before Insert/update, check the existence
of classid from class?[color=blue]
>From the point of performance, is this similar to the FK?[/color]