Foreign Key process

Question

I am trying to understand how foreign keys would work from an internal processing perspective (i'm using SQLServer). In other words, how would a table be impacted from the addition of a Foreign Key?

Say i have two tables(PK are Non Nulls):

TableA: PK Student_ID, StudentName, StudentAddress
TableB: PK Books_ID, FK Student_ID, Books_IssueMonth

select Student_ID from TableB where Books_IssueMonth=January;

When i run a sql statement in TableB calling on Student_ID, is the system told to first look it up in TableA and then pointed towards the clustered index (Books_ID) in TableB?

Would it be two-table seeks?

or would it be a seek in TableA and scan in TableB?

Answer 1

A foreign Key constraint on TableB has effect on three levels (at least).

1. When you INSERT INTO TableB a row where Student_ID is not null, the value of Student_ID is looked for on TableA. If it is not there, the insert will fail (the row will not be inserted into the database), if the insert is part of a larger transaction, the transaction itself will fail. UPDATEs of TableB involving column Student_ID behave in a similar manner (the new value for Student_ID is looked for on TableA).

2. When you UPDATE TableA SET Student_ID=<value> or DELETE FROM TableA any row, the old value of the column Student_ID will be searched for on TableB. This incurs some performance penalty (specially if the column is not indexed on TableB, but what's normally called a foreign key covering index). If the value is found then: if the FK constraint didn't specify what to do on UPDATE or DELETE, the UPDATE or INSERT will fail, because it would violate the constraint. If the FK specified what to do on UPDATE and/or DELETE, the specified action will be taken. If the condition is CASCADE this could trigger UPDATES/DELETEs on tables referenced by the referenced table, recursively.

3. On a SELECT, a FK constraint will not have any penalty at all. It can have some beneficial effects for the planner of the database. The condition TableB.Student_ID IS NULL OR TableB.Student_ID in (SELECT Student_ID FROM TableA) is a known fact for the planner, and this can be used to some advantage (for instance, if such condition exists on a WHERE clause, it can be replaced by a constant TRUE, and ease the processing). Another optimisation would be converting a TableB LEFT JOIN TableA USING (Student_ID) into an INNER JOIN, if Student_ID is known to be non-null, because the planner then knows all rows will be found on TableA. How much profit the different planners take of this knowledge will be heavily implementation dependent.

Selecting rows from TableB (if TableA is not specified in the FROM clause as well) will not have any effect on seeking Student_ID in TableA. Foreign Key constraints are enforced when data is INSERTEd or UPDATEd, and then known to hold when data is SELECTed.

---

NOTE: A FK constraint can specify what to do when the parent Student_ID is UPDATED or DELETED. The specific syntax can have small variations, but is typecally ON {UPDATE|DELETE} {RESTRICT|NO ACTION|SET DEFAULT|SET NULL|CASCADE}. Check how this works for PostgreSQL, MySQL, Oracle and SQL Server.

Answer 2

for the particular SELECT statement you asked about, there is no reason for the query to go off to Table A, since the query only mentions Table B. At least that's true for any database product I've worked with. You didn't say which database product you work with.

The question about what strategy it would use to find all the rows where Books_IssueMonth = "January", that would depend on what indexes exist on the table. If there's an index on the column in question, the query optimizer would almost certainly use that index to locate the relevant rows in a handful of disk accesses. If there's no index on the column in question, the query would have to do a full table scan on TableB, which could entail millions of disk accesses if there are enough rows in TableB.

The question becomes more interesting is you ask about a select that joins TableA and TableB. You didn't ask about that. in that case, you will need the column StudentID in TableB even if you don't declare it to be a FK.

As far as having a column like StudentID that function as a foreign key, but is not declared with a FK Constraint, That's generally a bad idea, for reasons outlined in joanolo's answer.