By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

How to define this kinds of constraint.

P: n/a
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.

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
If you can make class (classid) UNIQUE, you can make FK constraint from
Competition (classid) to class (classid).

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

P: n/a
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?


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.