foreign keys

[mufka]

Is it true that you cannot have two tables use the same field in a third table as a foreign key?

 

[r937]

depends on which table the foreign key is in

if it's in the third table, no you can't



r937.com | rudy.ca

 

[ingresman]

can you post an example, I don't see the issue !

 

[mufka]

r937 already confirmed my suspicion but here is an example.

Example:

Table_a has 2 fields (table_a_id, table_a_text)
Table_b has 2 fields (table_b_id, table_b_text)
table_c has 2 fields (table_c_id, table_c_text)

table_a_text uses table_c_text as a foreign key.
table_b_text wants to use table_c_text as a foreign key but can't because table_c_text is already used as a foreign key in table_a.

 

[ingresman]

I must be missing something here but why not...
Table_a (table_a_id, table_a_text,table_c_id)
Table_b (table_b_id, table_b_text,table_c_id)
table_c (table_c_id, table_c_text)

Am I missing something here about foreihn keys, which I thought were realy just pointers from one table another

 

[mufka]

I'm not sure if I'm not sure what you're saying

In my example, using a foreign key would ensure that whatever is in table_a_text(any particular row) has to already exist in table_c_text(any entry in the column)

I also want table_b_text(any particular row) to only be something that is already in table_c_text (any entry in the column).

I think (still not sure) your scenario adds an extra column to table_a and table_b when it isn't necessary if the foreign keys work - and I think this would break 3NF.

 

[DonQuichote]

Did you try it? Either I don't know what you are trying to do, or it is not a problem. I have, for instance, lots of tables linked to a "mutation" table that holds creation and update dates for records on lots of tables. I did not encounter a problem so far. As long as the foreign keys cannot conflict, there should not be a problem.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

could you please use something more descriptive than "table_a" and "table_b" and "table_c"

that's very confusing

what would be best is doing a SHOW CREATE TABLE for all three of them

and a few rows of sample data wouldn't hurt



r937.com | rudy.ca

 

[mufka]

Hmm. I had tried it, which prompted the question. Now I've tried it again and it seems to work ok. I must have had a typo in the script originally.

I read somewhere that you couldn't have table_a reference the same column in table_c as table_b references in table_c.

 

[ingresman]

I'd be interested to see the article if you can find it. You see relational databases don't have the kind of restriction you're suggesting.
In network databases (IDMSX) you could have the concept of a multi-member set where many member record types could have one single owner record, but only physical member record. All very much in the past now.
You got it working which is the main thing (wee that and I'm not loosing it !!!).

 

[r937]

I read somewhere that you couldn't have table_a reference the same column in table_c as table_b references in table_c.

ah, but you can! maybe you got your table names mixed up? ;-)

company (table_a) has state code that references states (table_c)

persons (table_b) has state code that references states (table_c)

see? quite feasible

r937.com | rudy.ca