Truncate Table examples
Go Up to Truncate Table
Consider a lottery drawing example:
TRUNCATE TABLE PENDING_LOTTERY_TICKETS DEFERRED;
INSERT INTO CURRENT_LOTTERY_DRAWING ... SELECT FROM PENDING_LOTTERY TICKETS;
COMMIT;
The day of the lottery drawing at 9:00 PM the TRUNCATE TABLE command is executed with a DEFERRED status. Because the Truncate Table command obtains an exclusive lock, any attempts to insert new lottery tickets at 9:00 PM have to wait. The CURRENT_LOTTERY_DRAWING table is then populated with PENDING_LOTTERY_TICKETS. The PENDING_LOTTERY_TICKETS table is truncated only after a successful COMMIT, this ensures the tickets are not lost before moving them for the current lottery drawing. Once truncation completes, the PENDING_LOTTERY_TICKETS exclusive lock is released, allowing pending lottery ticket INSERT commands to complete and be eligible for the next lottery drawing.
Conversely, a bulk load operation would want to ensure a table is immediately emptied before the load:
TRUNCATE TABLE CUSTOMER_ORDERS; /* IMMEDIATE is implied */
EXECUTE LOAD_CUSTOMER_ORDERS;
COMMIT;
A set of tables may form a composition hierarchy to represent the semantic notion of containment:
INVOICE_HEADERS <-- INVOICE_DETAILS <-- {RAIN_CHECK_TICKET, DROP_SHIP_ADDRESS}
The dependent tables are all declared with ON DELETE CASCADE foreign key constraints. All the invoices can be quickly dropped by executing:
TRUNCATE TABLE INVOICE_HEADERS CASCADE;
On the other hand, there may exist a lookup table of two-letter US State postal codes named POSTAL_CODES that every document in an organization depends on. None of these dependent tables register an ON DELETE CASCADE foreign key constraint with the lookup table.
| POSTAL CODE | STATE |
|---|---|
| CA | California |
| MA | Massachusetts |
| NC | North Carolina |
| TX | Texas |
| ... |
TRUNCATE TABLE POSTAL_CODES CASCADE;
Assuming that one or more of the foreign dependent tables are not empty, this command fails with a FOREIGN KEY CONSTRAINT violation error. The foreign dependent tables are not ON DELETE CASCADE and have storage allocated for their existing rows.