Truncate

Question

Truncate

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

sqlnaive SSCoach Points: 17437 More actions · Answer 1

sknox (9/30/2013)
Good question, but the answer isn't quite right.
You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint UNLESS that table has a foreign key that references itself.
implies that it's okay to truncate a table with foreign keys as long as there's a self-referential foreign key involved. A more accurate explanation would be
You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint UNLESS all foreign key references on the table are also from the table (i.e, self-referential.)
Try
create table PKFK (
PK int not null primary key,
FK int null foreign key references PKFK(PK)
);
insert PKFK values (1,null), (2,1), (3,2);
truncate table PKFK;
create table FK (
FK int null foreign key references PKFK(PK)
);
insert PKFK values (1,null), (2,1), (3,2);
insert FK values (1), (2);
truncate table PKFK;
drop table FK;
drop table PKFK;

sknox, you are right. you can truncate only if there is only self referential condition. However as per the condition provided in the query, the definition is correct.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 2

Nice question - Monday easy, but still interesting.

Back from two week vacation in Greece --> back to work --> back to SQLServerCentral - the "back to SQLServerCentral" is the only part I'm happy about. 😉

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 3

Good question, learnt something

Hope this helps...

Ford Fairlane
Rock and Roll Detective

TomThomson SSC Guru Points: 104773 More actions · Answer 4

sqlnaive (9/30/2013)
sknox (9/30/2013)
Good question, but the answer isn't quite right.
You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint UNLESS that table has a foreign key that references itself.
implies that it's okay to truncate a table with foreign keys as long as there's a self-referential foreign key involved. A more accurate explanation would be
You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint UNLESS all foreign key references on the table are also from the table (i.e, self-referential.)
Try
create table PKFK (
PK int not null primary key,
FK int null foreign key references PKFK(PK)
);
insert PKFK values (1,null), (2,1), (3,2);
truncate table PKFK;
create table FK (
FK int null foreign key references PKFK(PK)
);
insert PKFK values (1,null), (2,1), (3,2);
insert FK values (1), (2);
truncate table PKFK;
drop table FK;
drop table PKFK;
sknox, you are right. you can truncate only if there is only self referential condition. However as per the condition provided in the query, the definition is correct.

Well, the pedantic point of view might be that the answer is correct but the explanation isn't.

I could be even more pedantic than comrade sknox, if I wanted to be, and claim that the answer is wrong because the question doesn't say (at least not in so many words) run these as three separate chunks, each on its own so that each gets its own response; if you just concatenate the three bits (whether you include batch separators or not) the only messages you see will be the 1 row affected messages so there should be a "none of the above", but given the way the code is presented as 3 chunks and the "and the" before the last chunk that would be just plain silly.

I'm inclined to think that although sknox is 100% right about the explanation this is still a good question that has probably taught some people something, given that so far the two incorrect answers have received a 44% vote between them. And although it's fair to complain about the explanation, ih has to be recognised thatthe error is a natural rephrasing of the error in the referenced BOL page - just importing the sloppiness of BOL into the explanation..

Tom

jfgoude SSCrazy Points: 2586 More actions · Answer 5

easy one, do not use self-referencing every day but knowed this was the only one case where truncate table worked nice

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 6

I chose the obvious answer and got it wrong.

That's very interesting to know.

I wonder how other DBMS's would behave...

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Dazli SSC Veteran Points: 235 More actions · Answer 7

Dazli

SSC Veteran

Points: 235

December 12, 2013 at 3:52 pm

#1673110

good question.