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.
Contents
Creating a test database and tables
- Create a Database.
- Create a table named 'SOLO' that has no references from any other table.
- Create a table named 'SOLO_SELF_REF' and populate it with data, this table references itself.
- Next, create the primary table called PT, and add a primary key on EMP_NO.
- Create a table named "FT1" and add a foreign key reference, this references to PT with ON DELETE CASCADE.
CREATE DATABASE "truncate.ib";
COMIT;
CREATE TABLE SOLO (F1 INTEGER);
INSERT INTO SOLO VALUES (1);
COMMIT;
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;
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;
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
- First check the number of records on each table.
- Next, truncate the SOLO table.
- Next, truncate the SOLO_SELF_REF table with reference to self.
- Finally, check count of records on each table.
SELECT COUNT(*) FROM SOLO;
SELECT COUNT(*) FROM SOLO_SELF_REF;
TRUNCATE TABLE SOLO;
COMMIT;
TRUNCATE TABLE SOLO_SELF_REF;
COMMIT;
SELECT COUNT(*) FROM SOLO;
SELECT COUNT(*) FROM SOLO_SELF_REF;
Truncate a table with no references from other tables using the DEFERRED qualifier
- Check count of records
- 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.
- We still have access to the table. Do new DML requests.
- Check count of records
- COMMIT will truncate now due to DEFERRED action
- Check count of records
SELECT COUNT(*) FROM SOLO_SELF_REF;
TRUNCATE TABLE SOLO_SELF_REF DEFERRED;
SELECT * FROM SOLO_SELF_REF;
INSERT INTO SOLO_SELF_REF VALUES (5, 2);
SELECT * FROM SOLO_SELF_REF;
SELECT COUNT(*) FROM SOLO_SELF_REF;
COMMIT;
SELECT COUNT(*) FROM SOLO_SELF_REF;
Truncate on a primary table cascades to table references with ON DELETE CASCADE
definition
- Check count of records
- TRUNCATE PT table with default RESTRICT qualifier
- Check count of records
- TRUNCATE PT table with CASCADE qualifier
- Check count of records
SELECT COUNT(*) FROM PT;
SELECT COUNT(*) FROM FT1;
TRUNCATE TABLE PT;
COMMIT;
SELECT COUNT(*) FROM PT;
SELECT COUNT(*) FROM FT1;
TRUNCATE TABLE PT CASCADE;
COMMIT;
SELECT COUNT(*) FROM PT;
SELECT COUNT(*) FROM FT1;