CODEWITHSUNDEEP
database-design
heneryJ
heneryJ

Reputation: 11

FK question for parent tables

I have this:

table A
table B (fk to A)
table C (fk to B)
table D (fk to C)

Now when i do Inserts / Updates / Deletes on table D, i assume it will check the FK on table C, but will it further check up right to the parent A or stop at C? A good example is: Country -> State -> city -> Neighbourhood. All linked by FK. So if i do I/U/D on neighbourhood how far up will it check?

This will help be decide how far I can go with normalizing my data before i break it into too many child tables. If it will check all tables up then it will slow system down. But if it only checks the immediate parent then i can have hundreds of sub-child tables without any worries.

Upvotes: 1

Views: 91

Answers (3)

Kyle Burton
Kyle Burton

Reputation: 27528

The database should only need to check that the record (D) has a foreign key back to the table C - any time you insert/update/delete a record in C it would need to verify a corresponding record is referenced in B. Since no records can make it into C without having been verified vs B, then when inserting into D, only C would need to be checked.

Regardless, there are a lot of different concerns that go into physical data modeling. Reading about Data Modeling, for example: Snowflake and Star schema designs in Data Warehousing are good references to review.

Upvotes: 0

Amy B
Amy B

Reputation: 110071

Answer for SqlServer:

When you insert a record into table D, the foreign key checks that cID in the dRecord is found in table C. If cID is not found in table c, then the dRecord would be an orphan, and the insert forcefully fails.

The cRecord with that cID had its bID checked earlier, when it was inserted. It does not need to be checked again.

The only "chaining" behavior would be cascade delete. If the foreign keys have cascade delete behavior, deleting a bRecord will delete all related cRecords (with matching bID) and all dRecords related to those cRecords (with matching cID).

Upvotes: 0

Arun
Arun

Reputation: 3077

No, your inserts/updates and deletes on D will not be checked against B and A. The statement will only check against C. However you inserts on C would have already been checked with B. Therefore indirectly your inserts on D would be valid.

Upvotes: 2

Related Questions