Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Truncate Table

Posted on 2001-08-23
11
886 Views
Last Modified: 2012-08-14
I'm trying to do a Truncate table

ALTER TABLE txoCheckDetails NOCHECK CONSTRAINT ALL

TRUNCATE TABLE txoCheckRequest

but am getting the following error.

Cannot truncate table 'txoCheckRequest' because it is being referenced by a FOREIGN KEY constraint.

So my questions is sinze I am diabling the only foriegn key constraint on the table it will not let me Truncate the table.  I can do a DELETE on the table and remove everything, but how come I can't do a truncate.

0
Comment
Question by:Jagar
  • 5
  • 3
  • 3
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6419608
Truncate Table does not log this is the reason it is a lot faster than Delete, also the reason whe you cannot use it on Foreign Keys:  There could not be any rollback.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6419614
I meant where the table is referenced by a Foreign Key.
0
 
LVL 2

Author Comment

by:Jagar
ID: 6420101
is there any way I can disable the foreign keys?  Before running the truncate statement
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 18

Expert Comment

by:nigelrivett
ID: 6420103
Enterprise manager - right click - display dependencies.

or

Enterprise manager - right click on any table - generate scripts - remove the drop and create options - select indexes, foreign keys, primary keys - script to single file then search the file for the table name.

0
 
LVL 2

Author Comment

by:Jagar
ID: 6420136
I did that and there is only one table referencing that table.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 75 total points
ID: 6420148
The only way you can use Trucate Table is to remove the dependency run Truncate Table and then re-create the relationship.  Which will negate any advantage in using Truncate Table you had in the first place.

So I suspect your best choice is a Delete statement.
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6420190
disagree - removing foreign key - truncating table - adding foreign key with nocheck is fater on a large table if dangerous.

I remove all foreign keys from the database then add them all after the truncate - but I only ever do this if I am clearing all tables - otherwise why have foreign keys, if you remove them to truncate a table then you may as well not have them and maintain integrity in  the updates.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6420202
I stand corrected.  Thanks Nigel.
0
 
LVL 2

Author Comment

by:Jagar
ID: 6421709
 OK so then how would you recommend that I accomplish the following task.  Each night I need to rebuild 95% of table in my database from an Oracle database.  The other 5% of the tables are very large and we only do INSERTS of the new information to that table.
  Now I realize from what you are saying that I could use the delete statement or I could drop each foriegn key and rebuild them.  
  The problem with Deleting each table is I have do make sure and do them in a particular order, so that I drop the table that have no references and then work my way up.  But this besides being a pain is not very pratical since someone could add a new foriegn key or something at a later date and then the whole thing would fail, since the order did not take this new on into account.
  The problem with dropping each foriegn key and rebuilding them is basically the same if I don't know that a new one was added everything will again fail.
  So how can I accomplish this.
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6421756
How about this:
You have two types of table - one which gets rebuilt every night, one which remains.

Why not put them in different databases.
For the rebuild you can then drop all the tables and run the create script. Or hold an empty database backup and erstore it every night (need to kill connections though) Or use sql-dmo / dts to copy all objects from an empty database. Doesn't matter how you do it because this database isn't imoportant as it is not the source for any data.
Then you can access the database from the other database to extract. Means that the database which does hold data has permanent objects and is smaller - doesn't have all the deletes so won't get fragmented and will be easier to maintain.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6428617
Thanks for the points, although I think Nigel may have helped you with a solution.

Anthony
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question