| Author |
Topic |
|
TJ
Posting Yak Master
201 Posts |
 Posted - 2002-06-03 : 12:55:16
|
SQL 7.0 sp3We have a DTS that truncates SQL tables, then reloads the data from the dbIII tables. During the DTS we get the following error message:"Cannot truncate table because it's being reference by a Foreign Key Constraint." We can't figure out why. None of the tables that we're truncating have a foreign key set. ACK (table name) has a primary key set for indexing purposes. I've also posed the question that it might be more prudent to write some code that does a compare and inserts records not already in existence. Any opinions on that one? We need something that runs quickly whatever we end up doing.Can anyone point me in the right direction? Do I need to post more information? If so, please tell me.Thanks!Teresa"Someday I'll know enough to help someone else!" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-03 : 13:27:04
|
| The error msg is telling you that a foreign key constraint on some other table is referencing the one you are trying to trunc.I would say trunc & reload vs. insert/update/delete should be based on performance and uptime requirements. Performance will be based on the amount of base data and number of new/mods/delete. The uptime measure is a bit simpler: any time between when the trunc is issued and the reload finishes, the object/data will not be available. Whereas with pure dml you can manage locking.<O>Edited by - Page47 on 06/03/2002 13:27:43 |
 |
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-06-03 : 13:50:07
|
quote:
The error msg is telling you that a foreign key constraint on some other table is referencing the one you are trying to trunc.
I saw in BOL info on how to drop the Fkey in Enterprise Manager, but can't locate code that we could include in the DTS to drop the FKey, truncate the table, then add the FKey back in.Any other references I could check for this information? Thanks for your help! |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-03 : 14:00:07
|
To figure out which foreign keys reference this table, you can run sp_helpconstraint <table>. Once you know the name of the foreign key, you can temporarily disable it (don't forget to re-enable after you load your data in). As to your second question... Personally, I like the option of loading the data into a staging table and having a stored proc figure out what to do with it.Yes, it will be slower... However, there will be no downtime (since the old data will be there even while the load process is taking place). This will be especially important, if your load process bombs every once in a while (and you know it will  )... You never want to find yourself in the situation when you have truncated the table and weren't able to load the new data in...Just saw your second message... Use ALTER TABLE referencing_table NOCHECK CONSTRAINT constraint_name to disable foreign key constraint.Edited by - izaltsman on 06/03/2002 14:01:55Edited by - izaltsman on 06/03/2002 14:47:14 |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-06-03 : 15:16:22
|
quote:
This will be especially important, if your load process bombs every once in a while (and you know it will )... You never want to find yourself in the situation when you have truncated the table and weren't able to load the new data in...
Since I'm the epitome of 'Murphy's Law', I'll follow your advice! Thanks!  Teresa"Happiness is found along the way; not at the end of the road." |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-06-04 : 10:02:59
|
This is what I get when I run sp_helpconstraint ackObject Name--------------------ack constraint_type constraint_name status_enabled status_for_replication constraint_keys---------------------------------------------------------------------------------UNIQUE (non-clustered) IX_ACK_NEWRSN (n/a) (n/a) NEWRSNPRIMARY KEY (non-clustered) PK_ACK (n/a) (n/a) NEWRSNTable is referenced by-----------------------------------------------------------Efiling.dbo.ACKR: FK_ACKR_ACK Then I ran ALTER TABLE ackr NOCHECK CONSTRAINT FK_ACKR_ACK and received:The command(s) completed successfully. I ran the DTS again and I received the same error message.I looked at the foreign key constraints on the table and it doesn't appear that it's removing the check. Is it visible or done behind the scenes? Any clues as to why this will not work or what I'm doing incorrectly?Thanks!TeresaEdited by - TJ on 06/04/2002 10:12:04 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-04 : 10:36:39
|
quote:
...I looked at the foreign key constraints on the table and it doesn't appear that it's removing the check. Is it visible or done behind the scenes?...
It's not going to remove the constraint, rather it will disable it...However, even though the constraint is disabled, you still can't truncate the table: it's not allowed.quote:
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.--SQL Server 7, Books Online
Now, I know that doesn't address disabled constraints, but regardless you can't do it.So instead of NOCHECKing the constraint, you will have to DROP it and then reADD it after you load is done. Or you could DELETE your table like the quote says.<O> |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-06-04 : 10:46:08
|
quote:
quote:
...I looked at the foreign key constraints on the table and it doesn't appear that it's removing the check. Is it visible or done behind the scenes?...
It's not going to remove the constraint, rather it will disable it...However, even though the constraint is disabled, you still can't truncate the table: it's not allowed.quote:
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.--SQL Server 7, Books Online
Now, I know that doesn't address disabled constraints, but regardless you can't do it.So instead of NOCHECKing the constraint, you will have to DROP it and then reADD it after you load is done. Or you could DELETE your table like the quote says.<O>
Thank you! I didn't see that reference yesterday. I was looking at the 'Alter Table' information. I appreciate your help!Teresa |
|
|
|
|
DTS Error "Cannot truncate table..."