none
Could not drop object 'dbo.ParentTable' because it is referenced by a FOREIGN KEY constraint

    Question

  • I searched the forum to find right answer to match my situation but no luck.

    I am getting this error in a merge replication while syncing at subscriber. My parent table is in publication but not the child table. Parent and child tables are in the Publisher & subscriber DBs.

    1.Do I need to drop the relationships in subscriber side? If so, why?
    2.In publication properties, on Parenttable, do I need to set the property "Keep existing object unchanged" instead of the default "drop exisitng object and create a new one".

    Actually there is no data in parent,child tables in Subscriber db.  What is the remedy?

    TIA
    Friday, April 24, 2009 9:10 PM

Answers

  • As I understand it the problem is that you have 2 tables: TableA and TableB. TableA has the PK and TableB the FK. TableA is in the publication and you cannot include TableB in the publication. Both TableA and TableB exist at the subscriber and the FK relationship is also declared there. 

    Setting Include DRI=false will not help if this is the case.

    Please look at my other post: "You could have a pre-snapshot script to drop the FK and a post-snapshot script to add it back in."

    This will work ok if you don't have any deletes or changes to the PK records which have dependant FK records at the subscriber. If this is the case the post-snapshot script will fail when it tries to readd the FK.

    HTH,

    Paul Ibison, www.replicationanswers.com
    • Proposed as answer by Paul_Ibison Tuesday, April 28, 2009 8:44 AM
    • Marked as answer by Yum64147 Tuesday, April 28, 2009 3:56 PM
    Tuesday, April 28, 2009 8:29 AM

All replies

  • Hi,

        Merge replication is supposed to include both parent and child tables in a publication. Otherwise, how can you ensure semantic correctness in publication and subscription databases. If you do not maintain the parent-child relationship at subscriber (through a publication), subscriber can delete a row in the parent table, which has some corresponding child table rows. Then, sync will fail trying to upload the parent table delete. So, it is usually better to include both the tables.

        What is your scenario? Why do you need only the parent table in publication, but not the child table? Also, the tables are only not part of publication, but, created at subscriber?

    Thanks
    Udaya





    Saturday, April 25, 2009 11:20 AM
  • Make the foreign key constraint not for repliction on the publisher. This way a replication process can delete a parent without the constraint being enforced. As Udaya does point out this normally is not a good thing.

    You might want to evauate whether this really is what you want to do.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Saturday, April 25, 2009 11:21 PM
  • Hi Hilary - looking at the message title this is a different issue. Although the poster is talking about synchronization, the error actually occurrs on initialization.
    Cheers,
    Paul
    Monday, April 27, 2009 10:08 AM
  • Yes, you are quite correct Paul, the user will need to either drop the constraint from the table he is replicating, do not replicate constriants, or add the table to the publication.


    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, April 27, 2009 10:10 AM
  • You could add both tables into the publication as Udaya points out.
    You could have a pre-snapshot script to drop the FK and a post-snapshot script to add it back in.
    You mention that there is no data in the tables but presumably this is only a temporary state and a solution must cater for when these tables are populated....
    Changing to "Keep existing object unchanged" will also be ok, but I always prefer to populate from the publisher in case somehow the subscriber and publisher data get out of sync.
    HTH,
    Paul Ibison
    Monday, April 27, 2009 10:14 AM
  • Udaya,
    I have different subset requirements for multiple subscribers and this subscriber is not allowed to have the child data, only the parent data. Our schema is same in publisher and subscriber and the difference is only in data.
    Monday, April 27, 2009 3:02 PM
  • In that case you will most likely use the article option Include DRI=false for that article.

    Create your publication as normal, and then when you get to the article dialog in the merge publication wizard select the problem table, click on the drop down box for article properties, select Set Properties for Highlighted Table Article, and set Copy Foreign Key Constraints to false.

    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, April 27, 2009 3:14 PM
  • I am still getting the same error even after setting the "Copy FK Constarints" to false (Is this setting DRI=false?) . Do I need to stop and start the service?

    Thanks,

    Monday, April 27, 2009 6:53 PM
  • Could you help? I am stuck with this issue.
    Monday, April 27, 2009 9:50 PM
  • As I understand it the problem is that you have 2 tables: TableA and TableB. TableA has the PK and TableB the FK. TableA is in the publication and you cannot include TableB in the publication. Both TableA and TableB exist at the subscriber and the FK relationship is also declared there. 

    Setting Include DRI=false will not help if this is the case.

    Please look at my other post: "You could have a pre-snapshot script to drop the FK and a post-snapshot script to add it back in."

    This will work ok if you don't have any deletes or changes to the PK records which have dependant FK records at the subscriber. If this is the case the post-snapshot script will fail when it tries to readd the FK.

    HTH,

    Paul Ibison, www.replicationanswers.com
    • Proposed as answer by Paul_Ibison Tuesday, April 28, 2009 8:44 AM
    • Marked as answer by Yum64147 Tuesday, April 28, 2009 3:56 PM
    Tuesday, April 28, 2009 8:29 AM
  • Can  you post the problem tables so we can try to repro this?

    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Tuesday, April 28, 2009 10:02 AM
  • Thank you Paul, Hilary.

    Here is the DDL for the tables
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Duty]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[Duty](
    	[DutyID] [char](15) NOT NULL,
    	[DutyDescription] [varchar](35) NOT NULL,
    	[DutyType] [char](1) NOT NULL,
    	[SupportRecordStatus] [char](1) NOT NULL,
    	[ChangedDate] [datetime] NOT NULL,
    	[UserID] [char](7) NOT NULL,
    	[RecordEffectiveDate] [datetime] NOT NULL,
    	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[DutyID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DutyServices]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[DutyServices](
    	[DutyServiceID] [char](15) NOT NULL,
    	[DutyID] [char](15) NOT NULL,
    	[ServiceID] [char](5) NOT NULL,
    	[ChangedDate] [datetime] NOT NULL,
    	[UserID] [char](7) NOT NULL,
    	[RecordEffectiveDate] [datetime] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[DutyServiceID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Duty92389]') AND parent_object_id = OBJECT_ID(N'[dbo].[DutyServices]'))
    ALTER TABLE [dbo].[DutyServices]  WITH NOCHECK ADD  CONSTRAINT [Duty92389] FOREIGN KEY([DutyID])
    REFERENCES [dbo].[Duty] ([DutyID])
    GO
    
    IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Duty92389]') AND parent_object_id = OBJECT_ID(N'[dbo].[DutyServices]'))
    ALTER TABLE [dbo].[DutyServices] NOCHECK CONSTRAINT [Duty92389]
    GO
    Paul, How to add Pre and post snashop scripts? Using GUI?

    TIA
    Tuesday, April 28, 2009 2:14 PM
  • Yes - on the snapshot form of the Publication Properties you can "Run additional scripts". The before script will check to see if the FK exists and drop it. The After script will check if it doesn't exist and add it.
    Cheers,
    Paul Ibison
    Tuesday, April 28, 2009 2:33 PM
  • Thanks Paul,
    I have added the dropfk script before the snapshot and createfk script after the snapshot. Now I am able to synchronize but I do not see the FK on the child table. At this point it is not really matter because this subscriber is not dealing any with the child table. But I like to know what happend to the second script. I see the second script in the unc messages folder but I do not see the relationship in the database.

    Tuesday, April 28, 2009 3:17 PM
  • In this case I have only 1 child table and not a huge problem. But if I have more child tables, it is going to be more labor intensive. Is this the only way to replicate the parent tables?

    Tuesday, April 28, 2009 3:55 PM
  • I'd try running the script separately at the subscriber and see what the problem is.
    Cheers,
    Paul Ibison
    Tuesday, April 28, 2009 4:46 PM
  • Apart from having the related tables in the same publication, yes.

    By the way, AFAIR this does not happen with transactional replication, where there is sys.sp_MSdropfkreferencingarticle (http://www.replicationanswers.com/PreSnapshotScripts2005.asp). No equivalent in merge tho that I know of.

    Cheers,

    Paul Ibison
    Tuesday, April 28, 2009 4:52 PM
  • It is because this function is used for a computed column while defining table and while it is in use you cant Alter the function which in use.
    I think you can solve this problem by following steps
    1) Go to the table where this function is used
    2) Right click n go to Modify  
    3) Select computed column
    4) at below in Column Properties select Computed Column Specification
    5) Clear formula box (its showing function name)
    6) Alter your function
    I hope it will help you.
    Thank you
    Monday, June 13, 2011 3:11 PM