One or more rows contain values violating non-null, unique, or foreign-key constraints in SQL Script
- by Musikero31
Need help on this.
I'm just wondering why this error occurred. Below is the script concerned.
SELECT loc.ID
,loc.LocCode
,loc.LocName
,st.StateName
,reg.RegionName
,ctry.CountryName
,ISNULL(CONVERT(DATE, loc.UpdatedDate), CONVERT(DATE,loc.CreatedDate)) AS [ModifiedDate]
,stf.Name AS [ModifiedBy]
FROM Spkr_Country AS ctry WITH (NOLOCK)
INNER JOIN Spkr_Location AS loc WITH (NOLOCK) ON ctry.ID = loc.CountryID
INNER JOIN Spkr_State AS st WITH (NOLOCK) ON loc.StateID = st.ID
INNER JOIN Spkr_Region AS reg WITH (NOLOCK) ON loc.RegionID = reg.ID
INNER JOIN Staff AS stf ON ISNULL(loc.UpdatedBy, loc.CreatedBy) = stf.StaffId
WHERE (loc.IsActive = 1)
AND (
(@LocCode = '')
OR (
@LocCode <> ''
AND loc.LocCode LIKE @LocCode + '%'
)
)
AND (
(@RegionID < 1)
OR (
@RegionID > 0
AND loc.RegionID = @RegionID
)
)
AND (
(@StateID < 1)
OR (
@StateID > 0
AND loc.StateID = @StateID
)
)
AND (
(@CountryID < 1)
OR (
@CountryID > 0
AND loc.CountryID = @CountryID
)
)
The error probably occurred here INNER JOIN Staff AS stf ON ISNULL(loc.UpdatedBy, loc.CreatedBy) = stf.StaffId The requirement that I wanted is that if the loc.UpdatedBy is null, it will use the loc.CreatedBy column. However, when I used this, it generated the error mentioned.
In the database, the loc.CreatedBy is not null while the loc.UpdatedBy is nullable.
I checked it by running the script but it's working fine.
How do I do with it? What's wrong with my code? Please help.
