Can a FOREIGN KEY refer more than one table?

Question

I have a attribute which has to refer more that one table. like

CONSTRAINT FOREIGN KEY (officeid)
REFERENCES table1 or table2 or table3 .
i.e. officeid in the current table should present in any of the three tables(table1, table2 or table 3)

Is the constraint like above is possible. If yes could any one tell me the script for any database.

Answer 1

make a seperate table, which will hold all keys in all the 3 tables you have mentioned.

Answer 2

If the attribute is not always in table 1, then it is not a foreign key. Similarly for table 2 and table 3. For it to be a foreign key, it has to refer to the primary key of another table and has to contain either a value from that primary key or be null. So what you have here is not a foreign key as such. The values would have to be handled by an application unless you split the column into 3 and have each one as a foreign key onto its respective table.

Answer 3

Depends on your DBMS. In general, it should not be supported, because its not in line with basic SQL design.
I'd not recommend to do that even if possible. I'd recommend you make an additional table (Table0) that has only the OfficeID as primary key. You reference the table in question in Table0, but also Table1..3. That way you are sure IDs are not duplicated in Table1..3.

Answer 4

nicely, while you're speaking approximately having a customary key and distant places key in a single table bearing on a customary key and distant places key in yet another unmarried table, i'm thinking which you will opt to contain yet another table, noted as an "asccociative" table, to iron out that relationship. with the aid of fact in case you have a relationship like that, it sounds to me such as you could desire to finally end up with a many-to-many relationship, that's a no-no in relational db's, and could opt to be resolved with an associative table. as an occasion, enable's say you have 2 tables, products and Orders. below are the PK and FK assignments: products PK - prodD FK - orderID Orders PK - orderID FK - prodID Now, in a project like this, an order would have many products, and a unmarried product variety would desire to be secure on many orders. So, to settle in this, you could desire to create a table noted as "OrderProduct", which might have a composite PK, made from the orderid, and the productid, resolveing the wierdness with the PK and FK relationships. Does this make experience? i'm hoping so! sturdy success : )

Answer 5

Potentially yes since you can effectively write code for a constraint in some databases. However, could you explain a bit more about your database design. It usually isn't necessary for this to happen.

Answer 6

It usually helps to say which type of database are you using. Some version dont have foreign keys at all. SQL implementation differ from db creator to db creator and btween versions

Answer 7

Yes

Answer 8

you can in certain DB's like a MS SQL server you just make sure the foregin key is a primary key in the other table