'Column 'Id' is constrained to be unique' message in a View

support
1

I have the view, that itself works well (gives data with no error or duplicated id’s or whatever):

select
	io.refInventoryOperationTypeId,
	iod.TareWeight,
	ea.refBusinessPartnerId AS refBusinessPartnerId_Employee
from
	InventoryOperation io
	inner join InventoryOperationDetail iod on
		iod.refInventoryOperationId = io.Id
	left outer join InventoryItem oldII on oldII.Id = iod.refOldInventoryItemId
	left outer join InventoryItem newII on newII.Id = iod.refNewInventoryItemId
	left outer join EmployeeAsset ea on io.Id = ea.refInventoryOperationId

I added the field refBusinessPartnerId_Employee into Origam entity, with the lookup on BusinessPartner.Name.
After I added the field into the form, I get error message:

 Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

In the log there it is a message, that I don’t understand well:

2017-03-28 19:01:55,202 [3720] ERROR CZ.Advantages.Asap.DA.Service.AbstractSqlDataService [(null)] <origam> - 
Error: Column 'Id' is constrained to be unique. Value 'cc85fc1d-73ea-4720-b476-f5b17742cbb7' is already present.; Table:InventoryMovement
      * DataItems:; ; ; ; ; ; ; ; ; 0,0000; ; 0,0000; 0,0000; ; ; False; 0,0000000000; ; 4e9ce74a-92e3-4c1a-ae3f-426c157b007a; ; 307169dc-95be-4e39-8174-600b986a40c9; ; ; 307169dc-95be-4e39-8174-600b986a40c9; 27. 3. 2017 6:52:55; False; cc85fc1d-73ea-4720-b476-f5b17742cbb7; 27. 3. 2017 6:52:06; ; ; ; f6bb35f5-3117-43c5-90e6-60fdb38842ed; ; ; ; ; ; 0011608790; ST10024792; ; ; 27. 3. 2017 0:00:00; 0,0000; ; 8906447c-05c0-4af4-b338-5e0ef3199c49; b4a8491e-d864-4690-a27d-58764cee28b8; ; 67212b79-fde3-4907-b984-f8b8212cf0ae; ; False; 3262e47a-ac8a-4e99-9841-60d8b3300764; ; False; e1e09d81-1ab3-4fa4-86cf-4b54a4b99855; 4071b6ff-ebd1-4136-b29d-16b4470e88b3; ; 1,0000000000; ; ; ; ; ; ; ; ; ; 0,0000; 0,0000000000; ; ; ; ; 8bcd2876-5bf7-4c89-ad75-34c13d47b865; 639e6f9c-f849-41bc-866b-5959f74afd52; 
Error: Column 'Id' is constrained to be unique. Value 'cc85fc1d-73ea-4720-b476-f5b17742cbb7' is already present.; Table:InventoryMovement
      * DataItems:; ; ; ; ; ; ; ; ; 0,0000; ; 0,0000; 0,0000; ; ; False; 0,0000000000; ; 4e9ce74a-92e3-4c1a-ae3f-426c157b007a; ; 307169dc-95be-4e39-8174-600b986a40c9; ; ; 307169dc-95be-4e39-8174-600b986a40c9; 27. 3. 2017 6:52:55; False; cc85fc1d-73ea-4720-b476-f5b17742cbb7; 27. 3. 2017 6:52:06; ; ; ; f6bb35f5-3117-43c5-90e6-60fdb38842ed; ; ; ; ; ; 0011608790; ST10024792; ; ; 27. 3. 2017 0:00:00; 0,0000; ; 8906447c-05c0-4af4-b338-5e0ef3199c49; b4a8491e-d864-4690-a27d-58764cee28b8; ; 67212b79-fde3-4907-b984-f8b8212cf0ae; ; False; 3262e47a-ac8a-4e99-9841-60d8b3300764; ; False; e1e09d81-1ab3-4fa4-86cf-4b54a4b99855; 4071b6ff-ebd1-4136-b29d-16b4470e88b3; ; 1,0000000000; ; ; ; ; ; ; ; ; ; 0,0000; 0,0000000000; ; ; ; ; 8bcd2876-5bf7-4c89-ad75-34c13d47b865; 639e6f9c-f849-41bc-866b-5959f74afd52; 
    (etc...)

The problem is very probably because of last join.
But what another construction would I use?
Do you have idea how to solve it?

2

The last join most probably multiplies the resulting rows. So the Id gets multiplied as well and since it is defined as Primary Key in the entity, you are getting this error. Primary keys ought to be always unique.

The solutions:

  • choose a different Id to be a primary key of the result. The rule is: It must be the most detailed table so each resulting row must always have a unique Id. In your case the primary key might be EmployeeAsset.
  • do not include your table directly into the view - instead - create a relationship in the entity model so you can e.g. filter by the EmployeeAsset by choosing FilterParent in RelationType in the data structure entity.