CODEWITHSUNDEEP
mysqlforeign-key-relationship
Ajoo
Ajoo

Reputation: 63

Foreign keys from 2 tables make a PK in the 3rd

enter image description here

Hi, Can the Accounts table get its values from the SA & CA tables the fields SA and CA being the FK's constraints in the Accounts table?

The logic requires that I go like this and not the other way round. i.e. not build Accounts first as a supertable and then create the other two from there.

Thank you.

Upvotes: 0

Views: 24

Answers (1)

Drew
Drew

Reputation: 24949

There is no concept of inheritance and supertable as you suggest. It is not an OOP. So, create your tables with Foreign Key constraints and insert your data manually.

A Foreign Key value can be NULL. Though typically not a great idea. You might want to collapse your table from 3 down to 2 or 1 with a type column.

You may want to not have an Accounts table. What is even the purpose of it other than replication of information. Granted, it can handle more granularity, but don't over-populate your design with unnecessary tables.

If you are insistent on having separate Savings and Checking tables, and an Accounts table, the only way to pull off the Accounts table with FK's is with the use of NULLS. And, as mentioned, that is not recommended. The reason why? It is like saying It must be one of these ... except in the case that I don't care. And we should care about our data.

Resist the urge to have FK's NULL. One way to accomplish that is to have an accounts table, and join on other tables knowing only some will succeed. A Conditional Join. Achieved via LEFT JOIN.

Upvotes: 1

Related Questions