>>>Hi -
>>>I am trying to move some VFP data into SqlServer 2008 and establish a delete cascade between 2 tables. The import has been done but I am having trouble creating the relationship. Both tables have secondary unique indexes on fields with the same name (not the primary key). As far as I can tell, all the records in table 2 have a match in table 1. I can create the relationship in the diagram but when I attempt to save it I get an error that says :
>>>
>>>'Projects' table saved successfully
>>>'RTA-GeneralInfo' table
>>>- Unable to create relationship 'FK_RTA-GeneralInfo_Projects'.
>>>The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_RTA-GeneralInfo_Projects". The conflict occurred in database "TIP", table "dbo.Projects", column 'TipID'.
>>>
>>>TipID is the common field, Projects is the parent table, and RTA-GeneralInfo is the child table. Can someone suggest what I may be missing?
>>>Thanks
>>
>>As Naomi said,
>>you may have orphans.
>>Check them:
>>
>>SELECT [RTA-GeneralInfo].*
>>FROM [RTA-GeneralInfo]
>>LEFT JOIN Projects ON Projects.TipId = [RTA-GeneralInfo].TipId
>>WHERE Projects.TipId IS NULL
>>
>
>Thanks Borislav. Much easier than what I was doing with a scan.
Even simpler and better will be using
SELECT G.*
FROM [RTA-GeneralInfo] G where not exists (
select 1 from Projects P ON P.TipId = G.TipId)
If it's not broken, fix it until it is.
My Blog