I am studying SQL questions to improve my knowledge, and found this example:
Which statement is true about TRUNCATE and DELETE?
A. You can never TRUNCATE a table if foreign key constraints will be violated.
B. For large tables TRUNCATE is faster than DELETE.
C. For tables with multiple indexes and triggers DELETE is faster than TRUNCATE.
D. You can never DELETE rows from a table if foreign key constraints will be violated.
And supposedly only B is correct. I am not able to come up with a way to use delete in a way will violate foreign key constraints. Why isn’t D correct as well?
Here is an example:
create table question (id integer primary key );
create table answer (fk integer REFERENCES question (id));
insert into question values (1);
insert into answer values(1);
commit;
delete from question; << will not work due to the foreign key reference
delete from answer; << will work: now the question has no answers, but this does not violate the foreign key.
CodePudding user response：
The book question is tricky and might be written not completely correctly, but here is the deal: option B is correct, option C is obviously not correct.
The question in A and D is basically about ON DELETE Clause in Oracle, which allows you to delete referenced key values in the parent table that have dependent rows in the child table - Oracle Docs - ON DELETE Clause
Specify ON DELETE CASCADE if you want Oracle to remove dependent foreign key values.
Specify ON DELETE SET NULL if you want Oracle to convert dependent foreign key values to NULL.
So options A and D basically mean that in some cases we actually CAN DELETE rows or TRUNCATE table even if it's against foreign key constraints rules. Here are a lot of examples of using this clause.
Optional information: there is one more way to TRUNCATE table even if ON DELETE clause was not set: Oracle Docs - TRUNCATE TABLE
You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.
Example:
SQL> CREATE TABLE TEST_EMP (
CUST_ID NUMBER(10) NOT NULL PRIMARY KEY,
NAME VARCHAR2(30) NOT NULL,
MANAGER_ID NUMBER(10),
CONSTRAINT MAN_ID_FK FOREIGN KEY (MANAGER_ID)
REFERENCES TEST_EMP (CUST_ID)
);
SQL>
Table created.
SQL> INSERT INTO TEST_EMP VALUES (1,'John',NULL);
INSERT INTO TEST_EMP VALUES (2,'Maria',1);
INSERT INTO TEST_EMP VALUES (3,'Julia',1);
INSERT INTO TEST_EMP VALUES (4,'Steve',2);
commit;
SQL>
4 rows inserted.
SQL>
Commit complete.
SQL> TRUNCATE TABLE TEST_EMP;
Table truncated.
SQL> DROP TABLE TEST_EMP;
Table dropped.
CodePudding user response：
Technically you can have deferred constraints, which are not enforced until the end of the transaction. A TRUNCATE includes a commit so it doesn't make a difference there. However it would allow for a DELETE statement to violate a deferred constraint.
"The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE."