CAN A FOREIGN KEY REFERENCE MORE THAN ONE PRIMARY KEYS?
Posted by: amadenjo othello
Date: August 01, 2011 05:14AM

In the project that I am working on at the moment. The DEBTOR entity is made up of three different entities MEMBER(Members of the ORGANIZATION), ORG_CLIQUE(A sibling of ORGANIZATION with which it has financial donor/beneficiary relationship), ORG_FRIEND (Individuals or Organizations that are not related to ORGANIZATION but are in financial giver/receiver relationship).

DEBTOR entity goes like this:

CREATE TABLE `debtor` (

`debtorID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,

`debtorTypeID` TINYINT UNSIGNED NOT NULL ,

`debtor_type_originalID` SMALLINT UNSIGNED NOT NULL ,

`debtor_name` VARCHAR(255) NOT NULL ,

PRIMARY KEY (`debtorID`),
INDEX `fk_debtor_has_debtor_type1` (`debtorTypeID` ASC) ,
INDEX `fk_debtor_has_arm_friend1` (`debtor_type_originalID` ASC) ,

CONSTRAINT `fk_debtor_has_debtor_type1`
FOREIGN KEY (`debtorTypeID` )
REFERENCES `debtor_type` (`debtorTypeID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,

CONSTRAINT `fk_debtor_has_arm_friend1`
FOREIGN KEY (`debtor_type_originalID` )
REFERENCES `arm_friend` (`armFriendID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,

CONSTRAINT `fk_debtor_has_member1`
FOREIGN KEY (`debtor_type_originalID` )
REFERENCES `member` (`memberID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,

CONSTRAINT `fk_debtor_has_arm_clique1`
FOREIGN KEY (`debtor_type_originalID` )
REFERENCES `arm_clique` (`armID_1` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,

CONSTRAINT `fk_debtor_has_arm_clique2`
FOREIGN KEY (`debtor_type_originalID` )
REFERENCES `arm_clique` (`armID_2` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)

ENGINE = InnoDB,


Please i need to know if this schema acceptable?
The foreign key debtor_type_originalID references two PRIMARY KEYS in two different entities(memberID in MEMBER table, armFriendID in ARM_FRIEND table) and another COMPOSITE KEY(armID_1 & armID_2 in ARM_CLIQUE table ) in a third table.

I did it is this way because if I know the debtor_type, I could query the debtor_type table and get the Original Debtor's credentials.

Is this the right way to go about this?

Opinion will be highly welcome.



Edited 1 time(s). Last edit at 08/01/2011 05:15AM by amadenjo othello.

Options: ReplyQuote


Subject
Written By
Posted
CAN A FOREIGN KEY REFERENCE MORE THAN ONE PRIMARY KEYS?
August 01, 2011 05:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.