Adding Foreign key error

markogradymarkogrady Posts: 2
edited February 1, 2011 8:37AM in SQL Compare Previous Versions
The following error message was returned from the SQL Server:

[547] The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ProductPriceList_PriceLists". The conflict occurred in database "DemoData", table "dbo.PriceLists", column 'PriceListCode'.

The following SQL command caused the error:

ALTER TABLE [dbo].[ProductPriceList]

ADD CONSTRAINT FK_ProductPriceList_PriceLists] FOREIGN KEY ([PriceListCode]) REFERENCES [dbo].[PriceLists] ([PriceListCode]) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT [FK_ProductPriceList_Product] FOREIGN KEY ([ProductID]) REFERENCES [dbo].[Product] ([ProductID]) ON DELETE CASCADE

The following messages were returned from the SQL Server:

[0] Adding foreign keys to [dbo].[PurchaseOrderHeader]
[0] Adding foreign keys to [dbo].[SalesOrderHeader]
[0] Adding foreign keys to [dbo].[ProductPriceList]

I have two foreign keys being added on the original database. I have turned dependencies off. Would any body be able to direct me to resolve this issue.

Many thanks

Mark

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Mark,

    I can't say for sure, based on the information, but I think you may already have an FK called FK_ProductPriceList_PriceLists on the "PriceLists" table (assumption I made based on the constraint name). SQL Compare should take care of this for you, but I assume it didn't because you turned the "include dependencies" option off.

    Do you have a good reason not to use "include dependencies"?
  • This is how to reproduce this issue:
    1. create a table A with a column "OrderType" with a CHECK constraint so that this column can have values from the set { 'Cash', 'Card' }
    2. Sync another database with this schema
    3. Create a new table B with one column "OrderType" which is primary key.
    4. Now drop CHECK constraint from Step 1.
    5. Add foreign key on table A referring to table B.
    6. Now sync the db in step 2 again.

    Boom you will get this error.
  • I just tried following your reproduction steps myself, but I couldn't get the error - can you check you're running the latest patch from here? If you still have trouble I can check it out some more.
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.