INSERT and detect unique constraint error

It is not possible to use that statement as it is. A violation of a unique key will raise an error. You will have to catch that error, either with insert ignore (which does not allow you to react to your 3rd condition), or with on duplicate key.

You can use an on duplicate key that will intentionally throw an error if your conditions are met. Unfortunately, the errormessage will not be directly related to the violation, it will just be "any" exception (an exception that describes your situation does not exist anyway). In the update-part, check if the name is different. If it is unchanged, you do nothing ("ignore"), if it is changed, you can set an invalid value to e.g. throw a not null-error:

INSERT INTO devices_used_by_resource(id,sourcesId,name) 
VALUES(123,321,'the name')
on duplicate key update id = if(name = values(name), id, null);

> 1 row(s) affected

INSERT INTO devices_used_by_resource(id,sourcesId,name) 
VALUES(123,321,'the name')
on duplicate key update id = if(name = values(name), id, null);

> 0 row(s) affected

INSERT INTO devices_used_by_resource(id,sourcesId,name) 
VALUES(123,321,'the name1')
on duplicate key update id = if(name = values(name), id, null);

> Error Code: 1048. Column 'id' cannot be null

You have a second unique index on (name, sourcedId), that will also trigger on duplicate key. You didn't specifiy what should happen if you insert a row that violates this, so the statement will just ignore it (a violation of (name, sourcedId) will not change the name, so no exception is thrown):

INSERT INTO devices_used_by_resource(id,sourcesId,name) 
VALUES(1,321,'the name')
on duplicate key update id = if(name = values(name), id, null);

> 0 row(s) affected

If you want to throw an exception then too, you can compare all values instead of just the name, so a different id will also raise an error:

INSERT INTO devices_used_by_resource(id,sourcesId,name) 
VALUES(123,321,'the name1')
on duplicate key update 
id = if(id = values(id) and sourcesId = values(sourcesId) 
        and name = values(name), id, null);

> Error Code: 1048. Column 'id' cannot be null

The exception message is obviously not very clear about the error, because it raises an unrelated error. If you just want to catch that exception and know what it actually means, that will be fine. To make it a little more stylish, you could add a trigger that uses id=null as an indicator to throw a custom message, e.g.

delimiter $$
create trigger trbu_devices_used_by_resource 
  before update on devices_used_by_resource
for each row
begin
  if new.id is null then 
    SIGNAL SQLSTATE '45000' 
    SET message_text = 'Inserted duplicate entry with different attributes!';
  end if;
end $$  
delimiter ;  

It will also throw that error if you use update devices_used_by_resource set id = null without an insert, but I guess that does not happen that often, or maybe you can find a message that covers that too - or you do some more complicated communication between the upsert and the trigger, e.g. set the id or sourcesId to -812677 and check for that values in the trigger.