ERROR SETTING FK

Question

ERROR SETTING FK

lonhanner

SSC Eights!

Points: 839
More actions
January 26, 2013 at 10:18 am

#394031

I receive the following error when trying to set FK
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_TRIP_DETAILS_RIDER_NO". The conflict occurred in database "DCTSSQL ", table "dbo.RIDERS", column 'RIDER_NO'.
ALTER TABLE TRIP_DETAILS
ADD CONSTRAINT fk_TRIP_DETAILS_RIDER_NO
FOREIGN KEY (RIDER_NO)
REFERENCES RIDERS(RIDER_NO);

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's a RiderNo in trip details that does not have a matching entry in Riders

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

You have some orphaned data that you'll need to cleanup before adding the constraint.

SELECT RIDER_NO

FROM TRIP_DETAILS

WHERE RIDER_NO NOT IN (SELECT RIDER_NO

FROM RIDERS);

Or you can do this incrementally by adding the constraint WITH NOCHECK to in effect prevent the creation of new orphaned rows but tolerate the existing orphans, then cleaning up the bad data as time allows, and finally once all orphans are removed or resolved going back and CHECKing the constraint:

-- 1. add constraint but do not check existing rows

ALTER TABLE TRIP_DETAILS WITH NOCHECK

ADD CONSTRAINT fk_TRIP_DETAILS_RIDER_NO

FOREIGN KEY (RIDER_NO)

REFERENCES RIDERS(RIDER_NO);

-- 2. cleanup orphans as time allows...

-- 3. check the constraint to let the database engine record that it can rely on the existing data conforming to the constraint

ALTER TABLE TRIP_DETAILS CHECK CONSTRAINT fk_TRIP_DETAILS_RIDER_NO;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

lonhanner SSC Eights! Points: 839 More actions · Answer 3

lonhanner

SSC Eights!

Points: 839

January 28, 2013 at 9:48 am

#1581698

Thanks opc.three I got if fixed thanks to you.

Lon