SQL 大仙 求教

dalaohu

为什么 我 Truncate 一个 table 时不行，说有foreign key constraint.

但我 delete 就行呢？

这是怎么个说法？

月亮

delete 所有的还是只delete一条或几条记录？

holyplague

lz太懒了。

DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted. You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place. TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE. You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.

月亮

有foreign key constraint的不能用truncate，只能用不带where的delete

dalaohu

原帖由 月亮 于 2010-11-10 15:55 发表
delete 所有的还是只delete一条或几条记录？

是 delete 整个table

dalaohu

原帖由 月亮 于 2010-11-10 15:57 发表
有foreign key constraint的不能用truncate，只能用不带where的delete

我已经把其他所有reference table 全部都truncate 好了。

还是不能truncate 这个table。 只能delete。 doesn't make sense to me!

谁有功夫吧所有的contraint 全部drop 掉，再truncate！

SQL 太变态了，  I hate SQL

月亮

SQL一直如此变态。。

dalaohu

我发现只要帖子里有 SQL 的keyword, 就一定能把月亮勾搭进来
:)

乱码

原帖由 dalaohu 于 2010-11-10 16:13 发表
我发现只要帖子里有 SQL 的keyword, 就一定能把月亮勾搭进来
:)

同理，你如果想让她喜欢你，你脑门上也要贴个纸条，写着：SQL

这次注意不要写成“hello world”了

澳贼

http://stackoverflow.com/questio ... eign-key-constraint

澳贼

原帖由 乱码 于 2010-11-10 16:21 发表


同理，你如果想让她喜欢你，你脑门上也要贴个纸条，写着：SQL

这次注意不要写成“hello world”了

Hello SQL World

黑山老妖

可以临时disable constraint。然后truncate table。完了在enable constraint.

bulaohu

I'd just drop it & recreate....because I don't care.

黑山老妖

I do that too if it's in dev environment...
You will never know how many contraints you will violate if you just drop table...

dalaohu

Is there a SQL command

"Temparily disable all constraint"
And
"Now put all constraint back"?

Otherwise I hv to drop each constraint by their name

kr2000

原帖由 dalaohu 于 2010-11-10 18:08 发表
Is there a SQL command

"Temparily disable all constraint"
And
"Now put all constraint back"?

Otherwise I hv to drop each constraint by their name

sql server里有FOREIGN_KEY_CHECKS 这个值吗？我想应该可以disable所有的constraint的