.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Foreign key constraint not replicating

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
We have a merge replication setup with one publisher/distributor and several subscribers.  Several of the tables being replicated have foreign key constraints and these replicate without issue.  However, some foreign key constraints that utilize cascading do not replicate.  To make matters worse, some tables with these cascading fk constraints replicate fine while others do not.  Has anyone run across this behavior and is there a fix for it?  The cascades will dramatically reduce our merge conflicts and help maintain the integrity of the data so we need to keep them in place.

View Complete Post

More Related Resource Links

Create a sql foreign key constraint

Hi, I wish to create a constraint that state as below Code.CodeTable ( CodeID smallint, CategoryID smallint,....)  --> Parent Table Admin.Document( DocumentTypeID smallint,.....)  --> Child Table The FK will be Admin.Document.DocumentTypeID map with Code.CodeTable.CodeID I wish to have the contraint that only check Code.CodeTable.CodeID which the Code.CodeTable.CategoryID =15 only. Thanks.Meng Chew

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint


The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_Allergy". The conflict occurred in database "MSCPROJECT", table "dbo.Severity", column 'SeverityCode'.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblLinks_tblWebSite".


How does this happen? the tblLinks doesn't even have a column called websiteID.  websiteID is the primary key of the table tblwebsite. LinkID is the primary key of tblLinks. There is not LinkID column in tblwebsite.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblLinks_tblWebSite". The conflict occurred in database "Linkexchanger", table "dbo.tblWebSite", column 'WebSiteID'. The statement has been terminated.


So how does this happen? How do I terminate the foreign key relationship? I understand I can add this to my Insert stored procedure:

DROP CONSTRAINT FK_tblLinks_tblWebSite

but that stored procedure is used for other functions in my website, and I don't know if doing that is a good idea. Is there a way to relax the constraints for the purpose of this function here?  I'm taking data from a backup links table and trying to write a new row in the links table.

INSERT statement conflicted with COLUMN FOREIGN KEY constraint...


Hi there,

I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.

This is the error i get:

"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "


Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.

Thank you


insert statement violated because Foreign key constraint


I  create an SSIS package, i use data flow to import rows from different server to local server. Unfortunately, there always be error message something about insert statement violated because of foreign key "The INSERT statement conflicted with the FOREIGN KEY constraint" since the table is transactional table that has some foreign key. I want to minimize using temporary table and trying to use toolbox item in ssis.I would appreciate any since i am ssis newbie.


Replicating the Keyword match feature from Google GSA



We are trying to replicate a feature in sharepoint search that the Google Search Appliance has called "Keyword Match".  You choose a keyword or phrase and map it to a url.  When a user searches with that keyword or phrase the result link that you've matched it to comes up first in the list of results.  I'm trying to find a similar feature like this in Sharepoint OOTB search web parts.  Can you advise?  Thank you!

Mark De Souza

The DELETE statement conflicted with the REFERENCE constraint


I have created ASP.Net Dynamic data web site using scaffolding in Visual Studio 2010. When I click on delete in data grid for customer table I got below error message.

The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occured in database "../NORTHWIND.MDF", table "dbo.order details", column 'ProductId'

The statement has been terminated


SQL DIAGOSITC MANAGER tools capture "SELECT *" statement while ALTER / ADD CHECK CONSTRAINT is execu

Hello  ,   I exectuted the code(alter/add with check)  below in SSMS . My_table has 200 millions records . While running the alter/add with check query  the server crashed and the “SQL DIAGOSITC MANAGER” which run on the top of sql serever alert the DBA that select * from my_table was obtained from the last command issued by the user using a DBCC command.      Where does the select * from my_table comes from ? I didn’t excuted select * from my_table at all .   After the sql serever re started I was able to run the command below with out any problem. I checked the SQL PROFILER for the trace there is no “select * from my_table” .   So again where this select * from my_table comes from ?   Here is my initial query ----------------------------------------------------------------- ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key1] FOREIGN KEY([Key1]) REFERENCES [Dbo].[Dim_table1] ([Key1]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key1] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key2] FOREIGN KEY([key2]) REFERENCES [Dbo].[Dim_table2] ([key2]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key2] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key3] FOREIGN KEY([d], [c], [r], [g]) REFERENCES [Dbo].my

Duplicate constraint name

I have a 3.5 SDF file which contains duplicate FK constraint names. Is this a known bug? CONSTRAINT_TABLE_NAME CONSTRAINT_NAME UNIQUE_CONSTRAINT_TABLE_NAME UNIQUE_CONSTRAINT_NAME UPDATE_RULE DELETE_RULE Users_Log_Communications Users Users PK_Users Users_Log_Sensors Users Users PK_Users  From BOL: constraint_name The name of a constraint. The constraint_name is optional and must be unique within a database. If a constraint_name is not specified, SQL Server Compact 3.5 generates a constraint name. CASCADE CASCADE         Visit my SQL Compact blog - http://erikej.blogspot.com - Please mark as answer, if this was it.CASCADE CASCADE

How to get all the foreign key references for a primary key

Hi ,  I would like to know all the foriegn key references in the db for a table's primary key..How can i get that? Thanks sunil m datla

Primary Key Constraint Violation on Update

When UPDATING a single column (NOT the primary key column) of a transactionally replicated table, within MS SQL Server Management Studio, the replication monitor gives  Violation of PRIMARY KEY constraint 'PK_my_table. Cannot insert duplicate key in object 'my_table'. Statement Delivery is via the standard SCALL of dbo.sp_MSupd_my_table. The problem correlates with Statement Delivery of DELETE operations. That is, the problem exists if I specify "Do not replicate Delete Statements" and goes away if I specify standard behaviour for delete i.e a call on dbo.sp_MSdel_My_Table dbo.sp_MSupd_my_table does not appear to invoke deletion in any way and Im not updating the primary key value Any ideas Henrietta

Reinitialize transaction replication clear the subcriber data and replicating again

Hi all, I add new table in to mu publisher database and reinitialize the subscriber.And i select the option to create new snapshot and marked as reinitialize.When it starts the reinitializing it clear all data from subscriber and coping again.Am i missing any thing here to add new table into existing replication ? Regards, Theesh

Constraint and Trigger

What is the difference between Constraints and Triggers in SQL Server 2005?Explain Briefly?

trouble with foreign key

I have this statement that breaks due to a foreign key constraint. having a tough time figuring out how to get around it: delete   from Security_LoginActivity where   UserClientMetaDataId in ( select   a.UserClientMetaDataId from Security_LoginActivity a inner   join Security_UserClientMetaData b on a.UserClientMetaDataId = b.UserClientMetaDataId where   LoginTraceId is not null ) delete   from Security_UserClientMetaData where   LoginTraceId is not null and LoginTraceId < (   select min(LoginTraceId) + 50000 from Security_UserClientMetaData   where LoginTraceId is not null )   Error is: Msg 547, Level 16, State 0, Line 9 The DELETE statement conflicted with the REFERENCE constraint "FK_Security_UserClientSignature_Security_UserClientMetaData". The conflict occurred in database "ASPNETDB", table "dbo.Security_UserClientSignature", column 'UserClientMetaDataId'. The statement has been terminated.  qeqw

Inserting Foreign Key Value if it doesn't exist?

I am inserting a record and want to set the value for a foreign key but I am getting an error - I initally thought that it would insert it if it didn't exist but I must be wrong and want to confirm that I need to check all values and make a record for them if they don't exist... here's what I am trying to do - does his make sense?var newAllPart = new Part();                newAllPart.PartNumber = partNumber;                newAllPart.Manufacturer.ManufacturerName = partManufacturer; // error here if the Manufacturer doesn't exists already                newAllPart.PartType.PartTypeName = partType;                newAllPart.Category.CategoryName = partCategory;                newAllPart.DateCreated = DateTime.Now;                newAllPart.UserCreated = userCreated;                newAllPart.Package.PackageName = partPackage;    

Generics with Interface constraint from WCF.

Hello Not sure this is possible, but there might be a smart person out there who can answer it. Heck, maybe its simple :) I have a class that consumes an WCF service. That class contains the following generic method:   private void BuildTree<T, U>(T tree, U rootNode, ref List<U> listToBeBuilt) where T : ITreeDefinition, IEnumerable<T> where U : ITreeDefinition { ... }  Basically, it builds a tree based on generic data etc. However, it can't work without ITreeDefinition:   public interface ITreeDefinition { string GetRoot { get; set; } string GetChild { get; set; } }    This interface is contained in the WCF service, and used like so:   [DataContract] [KnownType(typeof(ITreeDefinition))] public class CaseStructureInfo : ITreeDefinition { #region OtherCode ... #endregion // Defines what properties to use to build a treetable. [DataMember] public string GetRoot { get { return SagsNr; } set { SagsNr = value; } } [DataMember] public string GetChild { get { return Hovedsag; } set { Hovedsag = value; } } }    The problem: The constraint "ITreeDefinition" is unknown to the generic method, because it is only visible in the WCF service. Can anyone help me out with this? Ps.  For the sake of understanding, here is the rest of the generic method... it is recursive, so

iterate till to get n-th level of foreign key inforamtion.

Hi.. how can i fetch n-th level of all foreign key's information? for example: one table is 'A', second table name is 'B', third tables is 'C' and so on table 'A' has a primary key, table 'B' has foreign key against table 'A' and table 'C' has foreign key of table 'B' and so on.. i want to make a such dynamic script which can get all/n-th level of foreign key relation inofrmations against. i made below script which can get foreign key information dynamically but it is used for single level not for n-th level. ALTER PROC [dbo].[sp_sng_fetching_foreign_key_info] @p_table_name varchar(100) , @flag bit AS DECLARE @pk_table_nme VARCHAR(150) DECLARE @fk_table_nme VARCHAR(150) DECLARE @pk_col_nme VARCHAR(150) DECLARE @fk_col_nme VARCHAR(150) DECLARE @constraint_nme VARCHAR(150) DECLARE @flg bit BEGIN set @flg = @flag DECLARE GETTING_ALL_FOREIGN_KEY_DATA CURSOR FOR select ISNULL(c.constraint_name,''), ISNULL(cfk.table_name ,''), ISNULL(kcu.column_name ,''), ISNULL(cpk.table_name ,''), ISNULL(pkt.column_name ,'') from information_schema.REFERENTIAL_CONSTRAINTS c INNER JOIN information_schema.TABLE_CONSTRAINTS cpk on c.unique_constraint_name = cpk.constraint_name INNER JOIN information_schema.TABLE_CONSTRAINTS cfk on c.constraint_name = cfk.constraint_name INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.constrai
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend