Tuesday, November 20, 2012

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

It Will happen when the primary key Field has null value which has been referenced to child table with foreign key relation.To avoid these kind of errors we need get the records which has null value in parent table.To get those details we need to use the below query .

select  * from Order O LEFT JOIN Sales s on O.Order_Id= s.Order_Id
WHERE s.Order_Id is null

If you are not get any data using this query ,we can solve in other using below queries

Second Method:
First i will get the records  which is not null  using foreign key filed.Then i will check the FK value with PK values .If any FK is referenced to null i will update record with existing PK value
select *from order where order_id is not null
update order  set order_id=4 where order_id=5


No comments: