How to define this kinds of constraint.

[shenanwei]

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.

[Tonkuma]

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

[Philip Sherman]

sh*******@gmail.com wrote:

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 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

[Knut Stolze]

Tonkuma wrote:

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

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

[shenanwei]

To make a trigger, everytime before Insert/update, check the existence
of classid from class?

From the point of performance, is this similar to the FK?