Feb 01 2023 10:05 AM
Feb 01 2023 10:05 AM
We are moving a solution from on-prem SQL to Dataverse and need to first do some clean-up. A SQL table, core_person_phone, is defined as:
CREATE TABLE [dbo].[core_person_phone]( [person_id] [int] NOT NULL, [phone_luid] [int] NOT NULL, [phone_number] [varchar](50) NOT NULL, [phone_ext] [varchar](50) NOT NULL, [unlisted] [bit] NOT NULL, [phone_number_stripped] [varchar](50) NOT NULL, [sms_enabled] [bit] NOT NULL, [sms_provider_luid] [int] NULL, [organization_id] [int] NOT NULL, CONSTRAINT [PK_core_person_phones] PRIMARY KEY CLUSTERED ( [person_id] ASC, [phone_luid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
One of the [phone_luid] choices is 282, which is Mobile Phone. On the Dataverse side, a Mobile Phone is unique to the table, and is a stand alone field. Converting this entity will first require that I make the SQL values unique across the table, not only by [phone_luid].
So here's what I'm trying to find an answer to: How to make a Mobile Phone (phone_luid = 282) unique to that type, and not allowed in any other phone type. There can be one Mobile Phone defined in the table, period.
Any help would be greatly appreciated!
Feb 01 2023 10:08 PM
@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
Feb 03 2023 05:51 AM
@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 );