Adding Foreign key error
markogrady
Posts: 2
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
[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
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"?
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.
Redgate Software