Truncate Table

Question

Truncate Table

Viewing 15 posts - 16 through 30 (of 32 total)

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

matthew.flower Default port Points: 1414 More actions · Answer 1

I have to say that learning that a self-referencing Foreign Key doesn't prevent Truncate while interesting is probably not of that much practical value.

But I will say thanks for highlighting the very abbreviated form of Foreign Key definition is interesting and I didn't know that it could be reduced that much.

gmby SSCarpal Tunnel Points: 4511 More actions · Answer 2

good question. but for the life of me, i cannot come up with a reason to use this (nor anyone else where I work)- anybody?

jg

chgn01 SSCarpal Tunnel Points: 4408 More actions · Answer 3

BOL - Restrictions

You cannot use TRUNCATE TABLE on tables that:

•Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

Do we have any chance to use a table that has a foreign key that references itself?

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

chgn01 SSCarpal Tunnel Points: 4408 More actions · Answer 4

Thank you for the posting.

We have many different ways to approach it. Is it a good idea or a bad idea?

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

db4breakfast Ten Centuries Points: 1097 More actions · Answer 5

gmby (3/28/2013)
good question. but for the life of me, i cannot come up with a reason to use this (nor anyone else where I work)- anybody?
jg

This scenario is often used for hierarchy. Stewart provided an example above.

--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.

Ken SSCoach Points: 17191 More actions · Answer 6

Outside of a hierarchy example, I can't think of when you would have a table foreign key reference to itself.

David Conn SSCertifiable Points: 6010 More actions · Answer 7

David Conn

SSCertifiable

Points: 6010

March 29, 2013 at 7:07 pm

#1602225

Thanks.

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 8

Dineshbabu

Hall of Fame

Points: 3220

April 1, 2013 at 1:53 am

#1602390

Nice one..

--
Dineshbabu
Desire to learn new things..

Toreador SSChampion Points: 11377 More actions · Answer 9

KWymore (3/29/2013)
Outside of a hierarchy example, I can't think of when you would have a table foreign key reference to itself.

A piece of machinery that is no longer available because it has been superseded by a different piece of machinery, something like

create table MachineryParts(

PartID int identity not null primary key,

PartDescription nvarchar(100) not null,

IsAvailable bit not null,

ReplacedByPartID int constraint fkMachineryPartsReplacement foreign key references MachineryParts

)

webrunner SSC-Dedicated Points: 31991 More actions · Answer 10

Strange situation, but informative.

Thanks for the question.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

angad.singh88 Old Hand Points: 300 More actions · Answer 11

+1

Angad

--Angad Singh
If I Stop Learning, I Cease to Be A SIKH !

demonfox SSCertifiable Points: 6289 More actions · Answer 12

nice and straightforwad - instinct worked for me ..:w00t:;-)

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

sqlnaive SSCoach Points: 17437 More actions · Answer 13

I got it correct. However assuming some other logic but anyways got corrected by the explanation and got one point (to my bonus)

jfgoude SSCrazy Points: 2586 More actions · Answer 14

not really a frequent usage, but an interesting question

B's-Data SSChasing Mays Points: 607 More actions · Answer 15

Good question but neither answer was correct. Granted you do NOT get an error but the truncate does not technically run either. If you go back and view the table data you will see that data has not been removed.

Truncate Table

Cookies on SQLServerCentral