[buro9]

I use FKs for most things in an RDBMS... but not for all things.

For example audit logs get no FKs, when a delete happens the audit logs about the deletion shouldn't be deleted.

I always FK a large table (millions or more rows) to a small table (tens to hundreds of rows).

But I will pause and ask hard questions about FK a large table to a large table... will this impact migrations? Do I need this FK? Is data integrity at risk without this FK even assuming a buggy app? Does the app utilise this FK for queries, or is there zero performance benefit from having the FK? If I don't have the FK are both tables always queryable by a PK? Should I have an index, potentially a UNIQUE index, in place of a FK?

Like most things... it depends. A dogmatic insistence on using them everywhere isn't always healthy, and the inverse is true that an avoidance everywhere isn't healthy.

The DB is there to store data and make it available, whilst enforcing data integrity... if it makes sense to use a FK to achieve those things do it, otherwise don't.

[buro9]

I should've added...

FK based on the possible size of a table rather than the current size of the table.

FKs are incredibly performance on a near-empty local dev database ;)

[vbezhenar]

> For example audit logs get no FKs, when a delete happens the audit logs about the deletion shouldn't be deleted.

    on delete set null

?

[Diggsey]

That still modifies the audit log, which is presumably supposed to be immutable. It also means you lose information, eg. if this is a user ID, then before you would be able to determine if two audit actions were done by the same (deleted) user. With "set null", you can't determine that anymore.

The parent's point is that the choice should depdend on the situation. Sometimes a foreign key is appropriate, sometimes it is not.

[masklinn]

> For example audit logs get no FKs, when a delete happens the audit logs about the deletion shouldn't be deleted.

Or updated. Or prevent the deletion.

But that’s configurable anyway.