Validations and Unique keys not firing on record creation

[Former Member]

Hi everyone,

In Data Manager SP3 I have a unique key set up on the repository main table to prevent duplicates. I have also have validations set up to enforce data integity rules. The validations are set for "error" (automatic validation).

In spite of these setups neither the unique key nor the validations are preventing duplicate or invalid records being added to the database. Unloading and then reloading the repository 'activates' the unique key errors and prevent the repository from loading. Also you can manulally run the validations and see the error messages popup and prevent updates of these records, but it does not run automatically.

Is the a setting that turns the MDM data integrity features on?

[Former Member · Accepted solution]

Hi Owen,

You mention that you have UK constraint and validations set up on the main table. Say, you defined fieldA+fieldB to tbe the unique key for main table. Then, if you are entering the same field A and field B for a second time, the repository gives you unique constraint violation error.

Similarly, UK errors fire up on flat tables too when you violate table level or field level UK.

Validations fire up on save of the record during manual entry. Make sure that the validations has callable set to know. Are your tables key map set to Yes or NO?

Thanks

Savi

[Former Member]

Thanks Rajani,

I tried you validation expression "IS_NOT_NULL(<i><Product Group Code></i>)"and it does trigger a validation error. However I have found it triggers the error whether or not the field is null.

When I use the expression "IS_NOT_NULL(Product Group Code)", it works.

How do the "i" parameters function in the expression?

[Former Member]

Hi Owen,

Please try using "AND" with all the fields.

Regards,

Rajani Kumar

[Former Member]

The unique key I have set up is a combination of 5 fields in the main table. It looks like Nulls in the unique key are what is allowing the records to save.

Does anyone have suggestions to get around this using the Data Manager?

[Former Member]

Hi Owen,

I did the following in order to understand your problem:

1. Created 4 fields in Main table: A,B,C,D.

2. Added all these four fields under "Unique Key" as a "Combination". So, when I click on the repository name in Cosole's left pane and then on the Main table name in Cosole's right hand side top pane, I can see all my four fields appended to each other with "+" for "Unique Fields". This indicates that I have added them as a combination.

3. Added two records with same values in all four of them and got a Pop Up saying "Unique constraint Voilated" which is understandable.

4. Added another record with only entering A,B and C and left D as blank. Record is saved without the "Unique Constraint Voilation" error.

In order to avoid the above scenario where "Null" values are allowed in a Unique Key combination, I have added a Validation(with Automatic Execution: Error) with the following expression and because of this, user is enforced to enter values in field without leaving them Null.

IS_NOT_NULL(<i><field Name></i>).

Hope this helps,

Regards,

Rajani Kumar

[Former Member]

Hi Conner,

<b>If you are using Qualified tables, here is a piece of info:</b>

1. Validations do not work on Qualified tables and will be addressed in SP4.

2. Unique key constraint also does not work with Non-Qualifier fields.

But, I am able to trigger validations automatically in Main and Flat tables and same is the case with Unique field setting. I am using MDM SP3 (5.5.28.0).

If you still have problem, You can try with SP3 Patch1 (5.5.28.17) which is now available for download.

Regards,

Rajani Kumar

[Former Member]

For the unique records, remember that null values are always allowed and do not trigger any errors...

Dirk