0

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?

  • 2
    You can join tables even if there's no foreign key. A foreign key can help in some databases, but not all. A foreign key is mostly used for data integrity for updates and inserts "check that the value in this cell exists in another column" – Neil McGuigan Jan 12 '17 at 22:55
4

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.

| improve this answer | |
  • 2
    May be you can add to your #1 that UPDATES behaves in a similar manner and in #2 that the child key lookup may incur some performance penalty, especially if there is no index on the FK column. – mustaccio Jan 12 '17 at 22:54
  • @mustaccio: thx for the remarks, already added. – joanolo Jan 12 '17 at 23:10
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.

| improve this answer | |
  • I have added the name of the dbms @WalterMitty – SQLserving Jan 13 '17 at 14:00
  • So if the PK is non-null and the FK is constrained, there will be no performance improvement in terms of data retrieval unless i create an index on the FK? From @Joanolo 's answer i understand that performance improvements can be created by using query manipulation in the SELECT statement but not for a simple SELECT statement. In essence, FK really only maintains referential integrity and improves the process of JOINS. – SQLserving Jan 13 '17 at 14:33
  • The primary purpose of declaring an FK constraint is referential integrity support. Performance considerations are very secondary. The primary purpose of including an FK, whether constrained or not, is implementing a relationship. I do not know if SQL Server creates any indexes when an FK constraint is declared. An index is created when a PK is declared, because enforcing uniqueness without an index would be a nightmare. If two indexes exist in two related tables, it's up to the query optimizer to decide when and how to use them. – Walter Mitty Jan 14 '17 at 2:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.