Orphans rows due to Missing Foreign keys

Question

I have composite foreign key missing in Production and when tried to create gave error "Alter table conflicted with foreign key constraint".

Below is the statement we are trying to execute.

ALTER TABLE dbo.tbl_ComputationWebsheetCellData WITH CHECK ADD CONSTRAINT [FK_tbl_ComputationWebsheetCellData_tbl_ComputationWebsheetCell] 
        FOREIGN KEY (ComputationID, CustomScheduleID, AccountGUID, ColumnGUID) 
        REFERENCES [tbl_ComputationWebsheetCell] (ComputationID, CustomScheduleID, AccountGUID, ColumnGUID)

Below are the tables details:

tbl_ComputationWebsheetCellData

--PK_tbl_ComputationWebsheetCellData (ComputationID, CustomScheduleID, AccountGUID, ColumnGUID)
--FK_tbl_ComputationWebsheetCellData_tbl_ComputationWebsheetCell 
 REFERENCES dbo.tbl_ComputationWebsheetCell (ComputationID, CustomScheduleID, AccountGUID, ColumnGUID)
tbl_ComputationWebsheetCell

--PK_tbl_ComputationWebsheetCell  (ComputationID, CustomScheduleID, AccountGUID, ColumnGUID)

Will the below script give the orphan rows?

select top 10 celldata.*
 from tbl_ComputationWebsheetCellData (nolock) celldata
 left join tbl_ComputationWebsheetCell(nolock) cell
 On celldata.ComputationID = cell.ComputationID
 and celldata.CustomScheduleID = cell.CustomScheduleID
 and celldata.AccountGUID = cell.AccountGUID
 and celldata.ColumnGUID = cell.ColumnGUID
 where cell.ComputationID IS NULL
 and cell.CustomScheduleID IS NULL
 and cell.AccountGUID Is NULL
 and cell.ColumnGUID IS NULL

Why does that mean you need to use `NOLOCK`? It has serious implications for data integrity, perhaps you should be using `SNAPSHOT` isolation instead — Charlieface, Sep 08 '22 at 16:21

Orphans rows due to Missing Foreign keys

0 Answers0