0

I am currently working on a piece of work where I have an issue trying to relate two tables, one of which is a sub class due to generalisation.

Table A has a primary key of staffID and I would like to relate this to a foreign key in a generalised table (Table B). The super class (table C) of table B has the staffID field and therefore the generalisation should carry this from table C in to Table B.

However when using the diagram view to set up a FK relationship, when I look in the drop down fields, I can only see the attributes in Table B and none of the super classes (Table C) attributes and hence not staffID.

To give more context to the situation, there are two types of staff, one can create data in all tables of the generalisation and so is linked directly to the super class by staffID, however the other type of staff can only add data to one of the sub classes.

               Super Class C
                 (staffID)

Sub Class D                     Sub Class B(FK) ----------- table A (PK)
                                                              (staffID)

Thanks in advance

1 Answers1

0

I'm not sure I understand your question. But in order to set a foreign key reference from B to C, the column C.staffID must be either a primary key, or it must have a UNIQUE constraint.

This SO answer might give you some useful guidance.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Apologies for being vague, staffID is not the PK of super class C. It is a FK from another table which is a super class of table A, hence how Table A also has a PK of staffID. –  Dec 20 '12 at 14:48
  • If C.staffID is not a primary key, and if C.staffID doesn't have a UNIQUE constraint on it, you can't set a foreign key reference to it. If you *can* declare C.staffID UNIQUE, then you should. If you can't, your superclass/subclass schema is wrong--table "C" is really not the superclass. – Mike Sherrill 'Cat Recall' Dec 20 '12 at 14:55
  • @Student5691: See new link in my answer. – Mike Sherrill 'Cat Recall' Dec 20 '12 at 15:02