Add Constraint

[StuartBombay]

I'm not sure what this is doing, but I'm guessing that it is checking the user table to make sure the ID (guid) exists in the user table before adding it to the user_group table?
This constraint exists on the user table:

ALTER TABLE [user_group]  WITH CHECK ADD  
CONSTRAINT [fkey$user_group$uguser] 
FOREIGN KEY([user_id])
REFERENCES [user] ([guid])

I'm confused because I get this error when I try to insert into the user_group table. I'm *sure* the users exists in the user table:

The INSERT statement conflicted with the FOREIGN KEY constraint "fkey$user_group$uguser". The conflict occurred in database "Touchy", table "user", column 'guid'.

There is a user table and a group table. The user_group table defines which group(s) the users belong to. Belonging to more than one group is allowed.
Here is the code for the insert, it's taking the data from a temp table:

INSERT INTO user_group
(
user_id, 
group_id, 
tps_receive_group_messages,
tps_membership_type,
tps_creation_user_guid,
tps_creation_date,
tps_last_update_user_guid,
tps_last_update
)
SELECT tpUserGuid, tpGroupGuid, a, b, cu, cdt, uu, udt
FROM NewUserGroups s
WHERE not exists (select user_id, group_id 
FROM user_group d
WHERE d.user_id = s.UserGuid AND d.group_id = s.GroupGuid)

Thanks for any help you can offer with this, maybe it's not the constraint that's causing the problem but rather the insert part?

 

[SQLSister]

You say you are sure the users exist in the users table but have you actually checked? Well clearly at least one does not or you wouldn't hit up against the constraint. Pull the results of your select and check them against the user table. In fact I would put the check right in my select if I were you by joining to the user table.

"NOTHING is more important in a database than integrity." ESquared

 

[StuartBombay]

Ran this against the database, no results so there are not any user guids in the insert table that are not in the user table.

SELECT NewUserGroups.UserGUID
FROM NewUserGroups LEFT JOIN user ON NewUserGroups.UserGUID = user.guid
WHERE user.tps_guid) Is Null

The data for the insert table originates from the user table, so it would be odd for the insert table to have users that are not in the user table, but I checked anyway.

I don't understand the constraint, is it looking for matching guids in the user table, or is it doing something else?