ALTER TABLE EA.TECHNOLOGY_UTILIZATION ADD CONSTRAINT TECHUTIL_FK_IT_1 FOREIGN KEY (INFORMATION_TECHNOLOGY_ID) REFERENCES EA.INFORMATION_TECHNOLOGY(INFORMATION_TECHNOLOGY_ID) go
when I execute this command I get this error
Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "TECHUTIL_FK_IT". The conflict occurred in database "test_db", table "EA.INFORMATION_TECHNOLOGY", column 'INFORMATION_TECHNOLOGY_ID'.
I thought there is some dependency or exisitng already.. for safe side.. dropped the constraint.. then the error message said
Msg 3728, Level 16, State 1, Line 1 'TECHUTIL_FK_IT' is not a constraint. Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. See previous errors.
Any insight.. of the issue here?
Answer by David Wimbush ·
I think the problem is that there are values in the TECHNOLOGY_UTILIZATIONADD column of INFORMATION_TECHNOLOGY_ID that aren't present in INFORMATION_TECHNOLOGY. Try this query to test:
select * from EA.TECHNOLOGY_UTILIZATIONADD tu left join EA.INFORMATION_TECHNOLOGY it on it.INFORMATION_TECHNOLOGY_ID = tu.INFORMATION_TECHNOLOGY_ID where it.INFORMATION_TECHNOLOGY_ID is null
That will show you the offending rows. You'll have to fix them before you can add the constraint.