1

With a ON DELETE RESTRICT restriction, I understand that you can't delete a row in the parent table if a reference exist in the child table, but is it expected behavior that even if the child table is actually empty you still cannot TRUNCATE the parent table?

Improve this question
6
  • TRUNCATE will fail even when ON DELETE RESTRICT is not specified. This operation uses fast records deleting technique which does not include FK checks.
    – Akina
    Commented Feb 20, 2020 at 16:34
  • 1
    Did you try SET FOREIGN_KEY_CHECKS=0; before running the truncate/delete statement?
    – Jesus Uzcanga
    Commented Feb 20, 2020 at 16:41
  • 1
    <snide-comment>Yet another reason why FKs may be more trouble than benefit</snide-comment>
    – Rick James
    Commented Feb 26, 2020 at 17:57
  • @RickJames Are you recommending to ditch FKs altogether?
    – IMB
    Commented Feb 26, 2020 at 20:24
  • FKs are designed for a small set of 'normal' integrity tests. TRUNCATE is a drastic action that is outside of the 'norm'. Please explain why you need to Truncate; perhaps there is another action that would achieve the goals and keep FKs.
    – Rick James
    Commented Feb 26, 2020 at 21:12

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.