Trouble Truncating Table Referenced by Forien Key

Question

Trouble Truncating Table Referenced by Forien Key

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Welsh Corgi (4/5/2016)
ok, I dropped the constraint but sometimes the error message does not include the table name.

Can you recreate the scenario where the error message does not include the table name?

If you can do that, and show us the error that does not include the table name (per the recreated scenario that you provide), then certainly somebody can show you how to fix that.

When the script will fail due to a referenced table (FK), then it should provide the table name. I haven't run into a case yet where it doesn't show that so I am very curious to see how to make it happen.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Just throwing this out there, but if you are deleting all the data from a set tables with RFI configured, you can delete the data starting at the lowest level table and work your way up the chain to the top most table without having to disable or delete the foreign key references unless you also have some ancillary tables that you need to leave intact.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I discovered that if I perform a Truncate I do not get a table name but if I do a Delete from SSMS I do get the table name.

I would usually take the approach to start from the child Tables and work my way up but this Database has 1,029 Tables in it.

I modified the Foreign Key Script to Sort by the referenced Table and that solved my problem.

Thanks everyone.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Welsh Corgi (4/5/2016)
I discovered that if I perform a Truncate I do not get a table name but if I do a Delete from SSMS I do get the table name.
I would usually take the approach to start from the child Tables and work my way up but this Database has 1,029 Tables in it.
I modified the Foreign Key Script to Sort by the referenced Table and that solved my problem.
Thanks everyone.

So basically what you said in the thread is not accurate?

You said you issue a delete (your truncate is even commented out) and then you sometimes do not get the table name in the error message.

All the way back to the basics then:

You CANNOT truncate a table with foreign keys on it. If you don't know what table you are trying to truncate then maybe you should involve your DBA.

Here is a TRUNCATE EXAMPLE on a table referenced by Foreign Keys and the error message that will result:

USE AdventureWorks2014;

GO

BEGIN TRANSACTION

TRUNCATE TABLE HumanResources.Department

Msg 4712, Level 16, State 1, Line 2
Cannot truncate table 'HumanResources.Department' because it is being referenced by a FOREIGN KEY constraint.

That right there tells you that it was a truncate that did not give you the name of the FK or the Parent table and that it was a TRUNCATE.

The code you used early in the post (as GAIL has pointed out more than once) will get you the FKs that you need to drop.

Now for the DELETE:

USE AdventureWorks2014;

GO

BEGIN TRANSACTION

DELETE HumanResources.Department

And the error:

Msg 547, Level 16, State 0, Line 12
The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeDepartmentHistory_Department_DepartmentID". The conflict occurred in database "AdventureWorks2014", table "HumanResources.EmployeeDepartmentHistory", column 'DepartmentID'.
The statement has been terminated.

So where exactly is the problem? You already have everything you need to perform the requested task - some of it posted by yourself.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I have everything taken care of.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Lynn Pettis (4/5/2016)
Just throwing this out there, but if you are deleting all the data from a set tables with RFI configured, you can delete the data starting at the lowest level table and work your way up the chain to the top most table without having to disable or delete the foreign key references unless you also have some ancillary tables that you need to leave intact.

Bump.

This is hugely significant but appears to have been ignored.

If this method can work for your task (there are caveats as Lynn points out) then it's a heck of a lot less messy than reading and storing a list of constraints, emptying the tables, then restoring the constraints.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

P Jones SSChampion Points: 12361 More actions · Answer 7

For those who don't know, the database diagrams in Management Studio are a good way of visualising the relationships set up between tables - just add a table to the diagram then right click and "add related tables" and the relationships show up.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

P Jones (4/6/2016)
For those who don't know, the database diagrams in Management Studio are a good way of visualising the relationships set up between tables - just add a table to the diagram then right click and "add related tables" and the relationships show up.

Yes I already did diagrams.

I am proficient with data modeling tools.

The problem has been resolved.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 9

Welsh Corgi (4/6/2016)
The problem has been resolved.

You keep saying that, but you haven't shared what resolved the issue. It would help people coming across this thread in the future if you posted your resolution.

Also you could mark this thread for your briefcase and keep it for future reference when you forget what you did to fix the problem in this instance. Which I do a lot for stuff I don't run into often.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.