Forum Discussion
Prep Data for Transfer to Dataverse
RonKochanowski , your post is not very clear and next time please post some sample data as DML statement and the expected result.
To get duplicate phone_number you can use this query:
SELECT phone_number, COUNT(*) AS Cnt
FROM dbo.core_person_phone
GROUP BY phone_number
HAVING COUNT(*) > 1
- olafhelper, Thanks for the reply, but a query to lookup duplicates is not what I'm looking for. I need a Constraint definition to prevent duplicates from occurring.
I need a Constraint definition to prevent duplicates from occurring.
RonKochanowski , ????
A primary key is such a constraint to avoid duplicate values, so what's the problem here; for me still unclear?
olafhelper , in the create code I added in the original message, you can see that the primary key is a compound key made up from the person_id and phone_luid fields. So there cannot be a duplicate combination of these combined.
The phone_luid for the Mobile Phone type is 282. So there can only be one Mobile Phone listed for a give individual, but the actual phone number can be assigned to multiple types. This means that the Mobile Phone number, 555-555-5555, can have type 282 once, but it can also have type 261 once, and type 296 once...and other people can also have that same Mobile phone number listed on their record. This is not the nature of a Mobile Phone. A Mobile Phone belongs to one person and should not be associated with others. This is how Microsoft defined the Mobile Phone field in Dataverse. So, I need to create a constraint in the current SQL environment that prevents the Mobile Phone field from having more than one phone_luid and belonging to more than one person.
A Constraint can be added to a SQL table through a key, and index, or through the CONSTRAINT command. What I'm looking for help with is the complexity of the command to achieve what I've described. So far this is what I've determined, but this does not prevent the phone number from having only 1 person_id:
CREATE UNIQUE NONCLUSTERED INDEX IX_MobilePhone_PhoneNumber_PhoneType ON dbo.core_person_phone( phone_number_stripped ASC, phone_luid ASC ) WHERE( phone_luid = 282 );
