Truncate Table FOREIGN KEY constraint work around SQL 2000 SP2 .608

Truncate Table FOREIGN KEY constraint work around SQL 2000 SP2 .608

Post by Matt » Fri, 03 May 2002 02:52:52



Good Afternoon All
Can someone explain the work around for this? I would prefer not to drop the
table everytime i want to truncate the table.

Here's the skinny, SQL 2000 SP2

TRUNCATE TABLE accounttransaction

Result:
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'accounttransaction' because it is being referenced by
a FOREIGN KEY constraint.

Thanks

 
 
 

Truncate Table FOREIGN KEY constraint work around SQL 2000 SP2 .608

Post by BP Margoli » Fri, 03 May 2002 03:04:30


Matt,

There is no work around ... to use the TRUNCATE TABLE command, you must drop
any all foreign key constraints that reference the table.

You can, of course, in SQL Server 2000 implement foreign key constraints
with the cascading delete option, and then just do a DELETE of the table,
although this will not have the performance of a TRUNCATE TABLE.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Good Afternoon All
> Can someone explain the work around for this? I would prefer not to drop
the
> table everytime i want to truncate the table.

> Here's the skinny, SQL 2000 SP2

> TRUNCATE TABLE accounttransaction

> Result:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'accounttransaction' because it is being referenced
by
> a FOREIGN KEY constraint.

> Thanks


 
 
 

1. Drop Table, SQL Server 2000, Foreign Key Constraints

Hi there,

Having created tables in SQL Server which contain Foreign
Key constraints, is there any way that I can drop these
tables without dropping the Foreign Key constraints first.
In Oracle you can specify the following:
'DROP TABLE XXX CASCADE CONSTRAINT'
which carries out the process fine.

Is there an equivalent in SQL Server 2000, or will I have
to generate code to drop all of the constraints from the
table before dropping the table itself.

Regards.

2. Informix for Win 95

3. Foreign Key Constraint - Truncate Table

4. Query is not working if i will use different language settings.

5. truncate table with foreign key constraints

6. How to dynamically get package ID and run package_Task?

7. SQL 2000 Foreign Key Constraint Anomaly

8. Upgrade Question-Will this work via Wizard?

9. BCP INTO A TABLE THAT CONTAINS PRIMARY KEY AND FOREIGN KEY CONSTRAINTS

10. SQL won't let me drop table, Foreign Key constraint error

11. ADOXCE Foreign Key Constraint NOT WORKING???

12. Working with foreign key constraints

13. Check Constraint vs. Foreign Key Constraint