Русский    English

DELETE statement page 3


  1. The CONTINUE IDENTITY and RESTART IDENTITY  options are supported, when unspecified  the CONTINUE IDENTITY option is implicit.
  2. Truncating of rows is available in more than one table when listing them via commas in a single operator.
  3. Cascade truncating can be done for tables having FOREIGN KEY constraints that reference the table:


Just a truncation takes place, but not only the deletion of related rows. I.e. if you add, among others, a row with NULL value in foreign key column to the child (referencing) table

  1. INSERT INTO Trun_Ref VALUES(1), (2), (NULL);
this row will be removed also.


  1. Oracle has not autoincrement function which could be specified in a column definition. Nevertheless autoincrement behaviour can be imitated with aid of sequence. For example, theTruncate_test table mentioned above could be created in Oracle in such manner:
    1. CREATE SEQUENCE u_seq
    2.  START WITH     5
    3.  INCREMENT BY   5;
    4. /
    5. CREATE TABLE Truncate_test (id INT PRIMARY KEY, val int);
    6. /
    7. INSERT INTO Truncate_test(id, val)
    8. VALUES (u_seq.NEXTVAL,1);
    9. INSERT INTO Truncate_test(id, val)
    10. VALUES (u_seq.NEXTVAL,2);
    11. INSERT INTO Truncate_test(id, val)
    12. VALUES (u_seq.NEXTVAL,3);

  2. Upon executing TRUNCATE TABLE Truncate_test command, the counter (sequence) is not being reset, and the numbering will be continued.
  3. Cascade operations are not allowed, so TRUNCATE TABLE statement is not available when the table is referenced by a foreign key.


  1. The CONTINUE IDENTITY and RESTART IDENTITY options are not supported; a counter (AUTO_INCREMENT) is being reset to zero.
  2. Cascade operations are similar to using DELETE statement, i.e. if foreign key has ON DELETE CASCADE option, related rows will be deleted only. This means that rows with NULL values in the foreign key column are being retained after truncating:
    1. TRUNCATE TABLE Truncate_test;
Bookmark and Share
Pages 1 2 3
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.