January 26, 2013 at 10:18 am
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);
January 26, 2013 at 10:43 am
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
January 26, 2013 at 12:26 pm
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
January 28, 2013 at 9:48 am
Thanks opc.three I got if fixed thanks to you.
Lon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply