6 Replies Latest reply: Aug 3, 2011 9:24 PM by 858930 RSS

    Difference between Truncate and Delete

    858930
      truncate table t_function_master*

      Error starting at line 4 in command:
      truncate table t_function_master
      Error report:
      SQL Error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
      02266. 00000 - "unique/primary keys in table referenced by enabled foreign keys"
      *Cause:    An attempt was made to truncate a table with unique or
      primary keys referenced by foreign keys enabled in another table.
      Other operations not allowed are dropping/truncating a partition of a
      partitioned table or an ALTER TABLE EXCHANGE PARTITION.
      *Action:   Before performing the above operations the table, disable the
      foreign key constraints in other tables. You can see what
      constraints are referencing a table by issuing the following
      command:
      SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

      delete from t_function_master;*

      19 rows deleted.


      As far as i know DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. As such, DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back. ( taken from website )

      I didn`t know that delete will not check on constraints ?? Is this truth ?? Is there some understanding i am missing for delete statement ?
        • 1. Re: Difference between Truncate and Delete
          sybrand_b
          You can't know that, as it is not true.
          The error message you got running the truncate command means:
          you still have enabled foreign keys pointing to the affected table, and those foreign keys should have been disabled ( a prerequisite for TRUNCATE) prior to the deed.

          You are obviously asking this question while you didn't look up the error message you got in the onlne dcoumentation.
          It is recommended you always look up error messages, so as to avoid redundant questions.
          This is a redundant question which should have been prevented by reading documentation.

          ------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Difference between Truncate and Delete
            Riaz
            As such, DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back. ( taken from website )
            Well, DROP and TRUNCATE can also be rolled back if you have configured flashback database and are using 10g.
            I didn`t know that delete will not check on constraints ?? Is this truth ?? Is there some understanding i am missing for delete statement?
            DELETE command will definitely check the constraints. So if you are deleting from master table and there is a child table, the behavior depends upon how you have defined foreign key relationship. From http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg05itg.htm#1992:

            Choosing How Foreign Keys Enforce Referential Integrity

            Oracle allows different types of referential integrity actions to be enforced, as specified with the definition of a FOREIGN KEY constraint:

            Prevent Update or Delete of Parent Key The default setting prevents the update or deletion of a parent key if there is a row in the child table that references the key. For example:

            CREATE TABLE Emp_tab (
            FOREIGN KEY (Deptno) REFERENCES Dept_tab);


            Delete Child Rows When Parent Key Deleted

            The ON DELETE CASCADE action allows parent key data that is referenced from the child table to be deleted, but not updated. When data in the parent key is deleted, all rows in the child table that depend on the deleted parent key values are also deleted. To specify this referential action, include the ON DELETE CASCADE option in the definition of the FOREIGN KEY constraint. For example:

            CREATE TABLE Emp_tab (
            FOREIGN KEY (Deptno) REFERENCES Dept_tab
            ON DELETE CASCADE);

            Set Foreign Keys to Null

            When Parent Key Deleted The ON DELETE SET NULL action allows data that references the parent key to be deleted, but not updated. When referenced data in the parent key is deleted, all rows in the child table that depend on those parent key values have their foreign keys set to null. To specify this referential action, include the ON DELETE SET NULL option in the definition of the FOREIGN KEY constraint. For example:

            CREATE TABLE Emp_tab (
            FOREIGN KEY (Deptno) REFERENCES Dept_tab
            ON DELETE SET NULL);

            +

            Also Truncate does not validate any constraints (hence it is not known at the time you execute this command, that whether it has got some child record or not. And I will not check it) the foreign key constraints must be disabled before you perform this operation. Checkout:

            http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:1192635578609
            • 3. Re: Difference between Truncate and Delete
              858930
              I know those constraints stuff but

              This is the sequence of my steps
              1. truncate
              2. Error
              3. delete
              4. 19 rows deleted.

              Was i high ? or was it cache ? I didn`t commit ? god knows..
              • 4. Re: Difference between Truncate and Delete
                Riaz
                Perhaps you didn't read my post above, completely.

                In order to truncate a table, all the foreign keys MUST be disabled (even if the child table has zero records). Since in your case, you had the active foreign keys, you got error.

                For Delete, it definitely checks the constraints. You didn't get any error because:

                (1) There were no records in the child table(s) OR
                (2) There were records in the child table(s) and the Foreign key relation was defined as CASCADE or SET NULL. So your delete statement was processed without any errors.

                Hope this is clear.
                • 5. Re: Difference between Truncate and Delete
                  John Spencer
                  When you use a delete statement, Oracle will check, for each row deleted, if a child record exists. It one does, then it will take the appropriate action (set null, cascade the delete, or throw an error) depending on how you have defined the foreign key.

                  Truncate is a DDL command and is done completely differently than a delete. Essentially, when you truncate a table, Oracle just updates the data dictionary. By default, it marks all extents used by the table beyond the first exent as unused, and moves the highwater mark to the beginning of the first extent. If you specify reuse storage, it simply moves the highwater mark to the beginning of the first extent.

                  The trucate command does not look at the data in the table it is truncating, so will have no way of knowing whether child records exist or not. Before truncating, it simply checks if there are enabled FK contraints pointing to the table. If there are, it throws an error and stops, if not, it truncates the table.

                  John
                  • 6. Re: Difference between Truncate and Delete
                    858930
                    ok thanks all again. Thanks for the comments. Solved =)