Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ERROR SETTING FK Expand / Collapse
Author
Message
Posted Saturday, January 26, 2013 10:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:25 AM
Points: 39, Visits: 144
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);
Post #1412046
Posted Saturday, January 26, 2013 10:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
There's a RiderNo in trip details that does not have a matching entry in Riders


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1412049
Posted Saturday, January 26, 2013 12:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 7,098, Visits: 12,606
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
Post #1412060
Posted Monday, January 28, 2013 9:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:25 AM
Points: 39, Visits: 144
Thanks opc.three I got if fixed thanks to you.

Lon
Post #1412517
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse