4

When running:

TRUNCATE TABLE YYYYY RESTART IDENTITY

I am seeing this error message:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "XXXXX" references "YYYYY".
HINT:  Truncate table "XXXXX" at the same time, or use TRUNCATE ... CASCADE.

Of the two suggestions in HINT:

  1. Using TRUNCATE ... CASCADE makes sense, and works, but is less explicit because one must inspect YYYYY to see where the cascade goes. That makes me want to try the other option:

  2. Truncate table "XXXXX" at the same time, but my question:

    What does it mean to truncate a table at the same time?


I tried adding a TRUNCATE XXXXX... (and wrapping them both in a BEGIN / COMMIT), but that yields the same error.

5
  1. What does it mean to truncate a table at the same time?

It means with the same statement. You can truncate more than one tables:

TRUNCATE xxxxx, yyyyy RESTART IDENTITY ;

More details in Postgres docs: TRUNCATE.

1

1) Cascaded truncates can be dangerous. If you have a lookup table (in this case, I believe XXXX), kiss it good-bye because it's going to be truncated and you'll lose all that data.

For example, you have a table of Customers (YYYY) with a FK to Orders (XXXX). When you truncate YYYY using a cascaded truncate, it would truncate YYYY. If not, it would display the truncate error you are seeing.

2) Truncate table 'XXXX' at the same time means that its suggesting you truncate your lookup/reference table as well.

  • You can truncate tables with FK, like Orders, fine. The problem is if you want to truncate Customers (the target of the FK). – ypercubeᵀᴹ Jul 28 '17 at 19:12
  • This is true. My statement wasn't clear, but I meant to address the riskiness of his cascaded truncate in that statement. – codedawg82 Jul 28 '17 at 19:21
  • Then fix your answer please. It's now claiming the opposite of what can happen. – ypercubeᵀᴹ Jul 28 '17 at 19:23
  • You still have it backwards. If there is an FK in Orders, referencing Customers, you can TRUNCATE Orders. No problem. But you can't TRUNCATE Customers;, it will give the error described in the question. – ypercubeᵀᴹ Jul 28 '17 at 19:41

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.