LinuxQuestions.org - Oracle SQL+

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Oracle SQL+ (https://www.linuxquestions.org/questions/programming-9/oracle-sql-183509/)

Hi
I'm using oracle i8 sql+ and i cant figure out the syntax to create a single foreign key to reference 2 constraints in different tables ie i need to combine the following 2 table alterations so they can be a single alteration or alternatively to create the key at table design time.

Code:

ALTER TABLE propetyForRent ADD FOREIGN KEY(OwnerNo) REFERENCES PrivateOwner(OwnerNo);

Code:

ALTER TABLE propetyForRent ADD FOREIGN KEY(OwnerNo) REFERENCES businessOwner(OwnerNo);

thanks :)

I don't think you can, dude. You're trying to say that propertyForRent.OwnerNo must exist in either PrivateOwner.OwnerNo or BusinessOwner.OwnerNo and I don't think you can do that.

AFAIK (and I should be ashamed if I don't know this, nearly 6 years as an Oracle developer and all) a foreign key constraint can reference only one primary key.

If you want this, I humbly suggest that your data model is wrong. Perhaps you should merge your BusinessOwner and PrivateOwner into a single table, with OwnerNo as its primary key, and an extra column to identify whether the owner is private or commercial. You could then use two views to simulate the BusinessOwner and PrivateOwner tables if you wished:

Code:

CREATE VIEW BusinessOwner AS SELECT * FROM Owner WHERE Type='BUSINESS';

CREATE VIEW PrivateOwner AS SELECT * FROM Owner WHERE Type='PRIVATE';

That might help you out.

Thanks for the reply and useful advice :)

Actually i'm following a diagram and table listing taken from a database book. I'm guessing that they must revise the structure later on and then implement it in the way you are suggesting.

In which case im doing ok :)