Truncate Table Tutorial

From InterBase

Go Up to Truncate Table


This section guides you in the use of the Truncate Table command and its qualifiers.

Creating a test database and tables

  1. Create a Database.
  2. CREATE DATABASE "truncate.ib";
    COMIT;
    
  3. Create a table named 'SOLO' that has no references from any other table.
  4. CREATE TABLE SOLO (F1 INTEGER);
    INSERT INTO SOLO VALUES (1);
    COMMIT;
    
  5. Create a table named 'SOLO_SELF_REF' and populate it with data, this table references itself.
  6. CREATE TABLE SOLO_SELF_REF (EMP_NO INTEGER NOT NULL, MNGR_NO INTEGER,
        PRIMARY KEY (EMP_NO));
    ALTER TABLE SOLO_SELF_REF ADD FOREIGN KEY (MNGR_NO) REFERENCES SOLO_SELF_REF (EMP_NO);
    INSERT INTO SOLO_SELF_REF VALUES (1, 1);
    INSERT INTO SOLO_SELF_REF VALUES (2, 1);
    INSERT INTO SOLO_SELF_REF VALUES (3, 2);
    INSERT INTO SOLO_SELF_REF VALUES (4, 2);
    COMMIT;
    
  7. Next, create the primary table called PT, and add a primary key on EMP_NO.
  8. CREATE TABLE PT (EMP_NO INTEGER NOT NULL, SSN_NO INTEGER NOT NULL);
    ALTER TABLE PT ADD PRIMARY KEY (EMP_NO);
    INSERT INTO PT VALUES (1, 100);
    INSERT INTO PT VALUES (2, 200);
    INSERT INTO PT VALUES (3, 300);
    INSERT INTO PT VALUES (4, 400);
    COMMIT;
    
  9. Create a table named "FT1" and add a foreign key reference, this references to PT with ON DELETE CASCADE.
  10. CREATE TABLE FT1 (MNGR_NO INTEGER NOT NULL, EMP_COUNT INTEGER, PRIMARY KEY (MNGR_NO));
    ALTER TABLE FT1 ADD FOREIGN KEY (MNGR_NO) REFERENCES PT (EMP_NO) ON DELETE CASCADE;
    INSERT INTO FT1 VALUES (1, 1);
    INSERT INTO FT1 VALUES (2, 2);
    COMMIT;
    

Truncate a table with no references from other tables

  1. First check the number of records on each table.
  2. SELECT COUNT(*) FROM SOLO;
    SELECT COUNT(*) FROM SOLO_SELF_REF;
    
  3. Next, truncate the SOLO table.
  4. TRUNCATE TABLE SOLO;
    COMMIT;
    
  5. Next, truncate the SOLO_SELF_REF table with reference to self.
  6. TRUNCATE TABLE SOLO_SELF_REF;
    COMMIT;
    
  7. Finally, check count of records on each table.
  8. SELECT COUNT(*) FROM SOLO;
    SELECT COUNT(*) FROM SOLO_SELF_REF;
    

Truncate a table with no references from other tables using the DEFERRED qualifier

  1. Check count of records
  2. SELECT COUNT(*) FROM SOLO_SELF_REF;
    
  3. Truncate the SOLO_SELF_REF table with DEFERRED qualifier. We now have exclusive access to the table. No other requests allowed to read/write to the table.
  4. TRUNCATE TABLE SOLO_SELF_REF DEFERRED;
    
  5. We still have access to the table. Do new DML requests.
  6. SELECT * FROM SOLO_SELF_REF;
    INSERT INTO SOLO_SELF_REF VALUES (5, 2);
    SELECT * FROM SOLO_SELF_REF;
    
  7. Check count of records
  8. SELECT COUNT(*) FROM SOLO_SELF_REF;
    
  9. COMMIT will truncate now due to DEFERRED action
  10. COMMIT;
    
  11. Check count of records
  12. SELECT COUNT(*) FROM SOLO_SELF_REF;
    

Truncate on a primary table cascades to table references with ON DELETE CASCADE definition

  1. Check count of records
  2. SELECT COUNT(*) FROM PT;
    SELECT COUNT(*) FROM FT1;
    
  3. TRUNCATE PT table with default RESTRICT qualifier
  4. TRUNCATE TABLE PT;
    COMMIT;
    
  5. Check count of records
  6. SELECT COUNT(*) FROM PT;
    SELECT COUNT(*) FROM FT1;
    
  7. TRUNCATE PT table with CASCADE qualifier
  8. TRUNCATE TABLE PT CASCADE;
    COMMIT;
    
  9. Check count of records
  10. SELECT COUNT(*) FROM PT;
    SELECT COUNT(*) FROM FT1;
    

Advance To: