Link One Column To Two Tables, Database- Mysql

[Tkays]

Hi There, l have created 3 tables in mysql, table A, table B and table C. Table B and C have a column - primary key with the same name and data type and length. l have created 2 foreign keys for Table B and C in table A (on the same column (column name = ID) in table A which links to the ID columns in Table B and Table C. The ID columns in Table B and Table C use auto increment and start at different numbers - for distinction purposes. Table B starts from 2 and table B starts from 5000. Both tables A and B have data in them.

This works so far. however, when l try to insert data into table A, its only checking if the foreign key exist in table B. How do l make it check table C as well???

Please Help. l need to populate table A ASAP.

In case you didn't get what l am trying to achieve, here's a detailed explanation.
Let me give you proper names to these tables, maybe you will understand what l am trying to achieve. Table A is Resource_Allocation. Table B is Hardware and table C is software. Both Software and Hardware have resourceID as primary key. Allocation has resourceID which has two foreign key constraints to link it to both the hardware and
l want to be able to allocate either hardware/ software in allocation using the resourceID. Is that possible??? lf so, software tables.
how do l go about it?

Many Thanks

 

[r937]

best solution is to make a new supertype table, Resource, which will have the auto_increment

then make Software and Hardware have their IDs as foreign keys to the Resource primary key ID

then you can FK the Resource_Allocation ID to Resource, and not to "both" of the other subtype tables (which isn't possible anyway)

for more info, do a search on subtype/supertype

r937.com | rudy.ca