Truncate Table

Question

Truncate Table

March 28, 2013 at 12:12 pm

This was removed by the editor as SPAM

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

EZ PZ

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

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 3

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: 4353 More actions · Answer 4

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: 4353 More actions · Answer 5

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 6

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 7

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 8

David Conn

SSCertifiable

Points: 6010

March 29, 2013 at 7:07 pm

#1602225

Thanks.

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

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: 11365 More actions · Answer 10

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: 31851 More actions · Answer 11

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 12

+1 🙂

Angad

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

demonfox SSCertifiable Points: 6289 More actions · Answer 13

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 14

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