April 5, 2016 at 7:20 am
ALTER TABLE ClientCoverage NOCHECK CONSTRAINT ALL
ALTER TABLE ClientCoverageException NOCHECK CONSTRAINT ALL
ALTER TABLE ContractorCoverage NOCHECK CONSTRAINT ALL
ALTER TABLE ContractorCoverageException NOCHECK CONSTRAINT ALL
ALTER TABLE Postal NOCHECK CONSTRAINT ALL
I'm getting errors when attempting to Truncate various tables.
For example to get the following error on one of the tables:
[Execute SQL Task] Error: Executing the query "
-- This code is used to verify the connection bef..." failed with the following error: "Cannot truncate table 'Postal_StateProvince' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I'm disabling the Foreign Keys as follows:
ALTER TABLE Postal_CityTownship NOCHECK CONSTRAINT ALL
ALTER TABLE Postal_CountyRegion NOCHECK CONSTRAINT ALL
ALTER TABLE Postal_StateProvince NOCHECK CONSTRAINT ALL
ALTER TABLE Postal_Country NOCHECK CONSTRAINT ALL
ALTER TABLE ClientCoverageType NOCHECK CONSTRAINT ALL
ALTER TABLE Postal_CountyRegion NOCHECK CONSTRAINT ALL
ALTER TABLE ProgramAreas NOCHECK CONSTRAINT ALL
ALTER TABLE TRADES NOCHECK CONSTRAINT ALL
I'm using the following script to identify FK Constraints:
SELECT
tab1.name AS
,
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column],
obj.name AS FK_NAME
--sch.name AS [schema_name]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
ORDER BY tab1.name
I'm using a SSIS Pages to do this.
Do you disable the constraint at the child level or the parent level?
I'm not providing much information but can someone give me insight as to what I need to do to resolver this issue?
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/
April 5, 2016 at 7:26 am
You cannot truncate a table that is referenced by a foreign key. It doesn't matter whether the constraint is enabled or not. If a table is referenced by a foreign key, truncate will fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2016 at 7:29 am
Further to Gail's comment, your options are to either change your TRUNCATEs to DELETEs, or DROP CONSTRAINT / TRUNCATE / CREATE CONSTRAINT.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 5, 2016 at 7:34 am
GilaMonster (4/5/2016)
You cannot truncate a table that is referenced by a foreign key. It doesn't matter whether the constraint is enabled or not. If a table is referenced by a foreign key, truncate will fail.
Thanks for your reply.
I check constraints in SSMS and I do not see any Foreign Key Constraints?
How do I drop the constraints and from Where?
I need to do it in a SSIS Task but I can do from the GUI.
Thank you.
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/
April 5, 2016 at 7:39 am
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>, instead of ALTER TABLE <table name> NOCHECK CONSTRAINT ALL. Make sure that you can put them back afterwards, and that they get put back exactly as they were before being dropped (not as they are now).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2016 at 7:42 am
Phil,
Thanks for your input.
I had already tried deleting before making this post but I got the same error.
Do I DROP the constraint from the referenced table or the table I'm getting an error on?
I have been unable to identify the referenced table(s).
I can not locate the FK Constraints in T-SQL or SSMS.
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/
April 5, 2016 at 7:47 am
The script you posted, that you said you use to identify the foreign keys, will do exactly that, identify the foreign key constraints. It gives the table name and the constraint name, which is all you need to drop it.
The referenced table name and referenced column name that the same script returns will be useful for generating a script to recreate them, you don't need them to remove the constraint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2016 at 8:09 am
Welsh Corgi (4/5/2016)
Phil,Thanks for your input.
I had already tried deleting before making this post but I got the same error.
--
Your error (as you posted originally) was this:
"Cannot truncate table 'Postal_StateProvince' because it is being referenced by a FOREIGN KEY constraint."
Are you seriously suggesting that this error occurred after issuing a DELETE?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 5, 2016 at 8:58 am
My bad. I thought that I delete would work.
The delete works sometimes
I execute the following and I get a FK error:
DECLARE @ServerName VARCHAR (25)
SET @ServerName = @@ServerName
IF @ServerName = 'TESTDATA2V'
BEGIN
--- TRUNCATE TABLE Postal_CityTownship
DELETE
FROM Postal_CityTownship
END
Error: The DELETE statement conflicted with the REFERENCE constraint "FK_Postal_CityTownship". The conflict occurred in database "PrData", table "dbo.Postal_Original", column 'CityTownshipID'.
I'm still trying to determine which tables to Drop the constraint on.
I made some progress but I'm missing a few constraints so far.
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/
April 5, 2016 at 9:05 am
The error message provides that information, if you read it closely enough.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 5, 2016 at 9:12 am
go this table "dbo.Postal_Original" and drop FK_Postal_CityTownship constrinat
then try with delete command, it works
if still repeats , you have to keep on drop the constraint or delete from that table depends on your environment (make a decision delete row or drop constraint)
April 5, 2016 at 9:29 am
Welsh Corgi (4/5/2016)
I'm still trying to determine which tables to Drop the constraint on.
Read your error messages.
Error: The DELETE statement conflicted with the REFERENCE constraint "FK_Postal_CityTownship". The conflict occurred in database "PrData", table "dbo.Postal_Original", column 'CityTownshipID'.
Or, use the script which you posted earlier, because that's what that script does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2016 at 10:08 am
ok, I'm missing something?
DELETE
FROM Postal_CountyRegion
Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_ContractorCoverage_Postal_CountyRegion". The conflict occurred in database "PrData", table "dbo.ContractorCoverage", column 'CountyRegionID'.
The statement has been terminated.
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/
April 5, 2016 at 10:10 am
Read Your Error Messages!
The DELETE statement conflicted with the REFERENCE constraint "FK_ContractorCoverage_Postal_CountyRegion". The conflict occurred in database "PrData", table "dbo.ContractorCoverage", column 'CountyRegionID'.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2016 at 10:32 am
ok, I dropped the constraint but sometimes the error message does not include the table name.
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/
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply