I have a relational database in my server where I've used for developing a system. Now I want to make it live and truncate all data from the tables. I've manually deleted data from tables and after that I've run the truncate command, but it show this error :
我在我的服务器中有一个关系数据库，我用它来开发系统。现在我想让它生效并截断表格中的所有数据。我已经手动删除了表中的数据，之后我运行了truncate命令，但它显示了这个错误：
Cannot truncate table 'dbo.Building' because it is being referenced by a FOREIGN KEY constraint.
无法截断表'dbo.Building'，因为它正被FOREIGN KEY约束引用。
Is there any way to empty my database by using a single command? I've searched google, all of them told to use truncate command. But I can not use it for all the tables because the error occurred.
有没有办法使用单个命令清空我的数据库？我搜索过谷歌，所有人都告诉他们使用truncate命令。但我不能将它用于所有表，因为发生了错误。
I want to entry data from the ID no 1 in all tables.
我想在所有表中输入ID为1的数据。
Please give me a guideline to truncate all the data from my database.
请给我一个截断我数据库中所有数据的指南。
2 个解决方案
#1
3
Now I want to make it live and truncate all data from the tables
现在我想让它生效并截断表格中的所有数据
You are approaching this completely wrong. Even if you succeed, you will deploy a system which will be impossible to upgrade. As you continue to develop you will modify the development database and then when you have to deploy your next version of your application you'll realize you need to modify the production database and keep all of its data.
你正在接近这个完全错误。即使您成功，您也将部署一个无法升级的系统。在继续开发的过程中，您将修改开发数据库，然后在必须部署下一版本的应用程序时，您将意识到需要修改生产数据库并保留其所有数据。
Stop the deployment right now and go back to the drawing board to design a proper deployment strategy. I recommend migrations. Another alternative is using diff tools.
立即停止部署并返回绘图板以设计适当的部署策略。我建议迁移。另一种选择是使用diff工具。
Truncating tables is completely irrelevant for what you're actually trying to achieve.
截断表格与您实际尝试实现的内容完全无关。
#2
1
There are two options I could think off..
我可以考虑两种选择..
-
You need to drop (not just disable ) all foreign keys, then finally run truncate to delete all table data using any method.. and finally recreate all foreign keys
您需要删除（而不是仅禁用）所有外键，然后最终运行truncate以使用任何方法删除所有表数据..最后重新创建所有外键
-
You also can script out only DDL and deploy database using that script instead of providing database to deployment team..
您还可以使用该脚本仅编写DDL脚本并部署数据库，而不是向部署团队提供数据库。
#1
3
Now I want to make it live and truncate all data from the tables
现在我想让它生效并截断表格中的所有数据
You are approaching this completely wrong. Even if you succeed, you will deploy a system which will be impossible to upgrade. As you continue to develop you will modify the development database and then when you have to deploy your next version of your application you'll realize you need to modify the production database and keep all of its data.
你正在接近这个完全错误。即使您成功，您也将部署一个无法升级的系统。在继续开发的过程中，您将修改开发数据库，然后在必须部署下一版本的应用程序时，您将意识到需要修改生产数据库并保留其所有数据。
Stop the deployment right now and go back to the drawing board to design a proper deployment strategy. I recommend migrations. Another alternative is using diff tools.
立即停止部署并返回绘图板以设计适当的部署策略。我建议迁移。另一种选择是使用diff工具。
Truncating tables is completely irrelevant for what you're actually trying to achieve.
截断表格与您实际尝试实现的内容完全无关。
#2
1
There are two options I could think off..
我可以考虑两种选择..
-
You need to drop (not just disable ) all foreign keys, then finally run truncate to delete all table data using any method.. and finally recreate all foreign keys
您需要删除（而不是仅禁用）所有外键，然后最终运行truncate以使用任何方法删除所有表数据..最后重新创建所有外键
-
You also can script out only DDL and deploy database using that script instead of providing database to deployment team..
您还可以使用该脚本仅编写DDL脚本并部署数据库，而不是向部署团队提供数据库。