Foreign key refering to primary keys across multiple tables?

Question

Hi!

I have to two tables namely employees_ce and employees_sn under the database employees.

They both have their respective unique primary key columns.

I have another table called deductions, whose foreign key column I want to reference to primary keys of employees_ce as well as employees_sn. Is this possible?

for example

employees_ce table
empid   name
khce1   prince

employees_sn table
empid   name
khsn1   princess

so is this possible?

deductions table
id      name
khce1   gold
khsn1   silver

Answer 1

Technically possible. You would probably reference employees_ce in deductions and employees_sn. But why don't you merge employees_sn and employees_ce? I see no reason why you have two table. No one to many relationship. And (not in this example) many columns.

If you do two references for one column, an employee must have an entry in both tables.

Answer 2

Yes, it is possible. You will need to define 2 FKs for 3rd table. Each FK pointing to the required field(s) of one table (ie 1 FK per foreign table).

Answer 3

You can probably add two foreign key constraints (honestly: I've never tried it), but it'd then insist the parent row exist in both tables.

Instead you probably want to create a supertype for your two employee subtypes, and then point the foreign key there instead. (Assuming you have a good reason to split the two types of employees, of course).

                 employee       
employees_ce     ————————       employees_sn
————————————     type           ————————————
empid —————————> empid <——————— empid
name               /|\          name
                    |  
                    |  
      deductions    |  
      ——————————    |  
      empid ————————+  
      name

type in the employee table would be ce or sn.

Answer 4

You cant define two foreign keys on same column to link to two tables. I think as a workaround what you can do is to create two columns halllocation & flatlocation instead of location. then link halllocation to 'hall' (column ID:'locNo') via a foreign key and also link flatlocation to 'student_flat' (column ID:'flatNo') by means of another foreign key. This ensures both the columns will have only allowed values from master table. Now to make sure you've either one of columns having a value in each record of bedroom create a check constraint to ensure either of them is not null. so it will be like

CONSTRAINT FK_bedroomFlatLoc FOREIGN KEY (flatlocation) REFERENCES student_flat(flatNo) , CONSTRAINT FK_bedroomHallLoc FOREIGN KEY (halllocation) REFERENCES hall(locNo) CONSTRAINT CK_bedroomLocationHallOrFlat CHECK ( (flatlocation IS NULL AND halllocation IS NOT NULL) OR (flatlocation IS NOT NULL AND halllocation IS NULL) )

I found this solution here : http://forums.techarena.in/software-development/1125535.htm

I tried it, derby is reporting constraint voilated at the third constraint (i.e., CK_bedroomLocationHallOrFlat ), I can't figure out why :-s

Answer 5

Assuming that I have understood your scenario correctly, this is what I would call the right way to do this:

Start from a higher-level description of your database! You have employees, and employees can be "ce" employees and "sn" employees (whatever those are). In object-oriented terms, there is a class "employee", with two sub-classes called "ce employee" and "sn employee".

Then you translate this higher-level description to three tables: employees, employees_ce and employees_sn:

• employees(id, name)
• employees_ce(id, ce-specific stuff)
• employees_sn(id, sn-specific stuff)

Since all employees are employees (duh!), every employee will have a row in the employees table. "ce" employees also have a row in the employees_ce table, and "sn" employees also have a row in the employees_sn table. employees_ce.id is a foreign key to employees.id, just as employees_sn.id is.

To refer to an employee of any kind (ce or sn), refer to the employees table. That is, the foreign key you had trouble with should refer to that table!

Answer 6

Hi I am feeling well right now, because have you given me, such a very nice link. Thanks you so much. Can you tell me , how to connect two tables, if it is possible ? please send me.some example