Add check constraint to MSSQL table

I have a Great Plains customer table and I need to create a constraint on the custnmbr field which checks that the value is 0-9 and the length of the value cannot be greater than 6 positions.

What is the bast way for me to create a check constraint on the table (RM00101)?
jdr0606Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ares KurkluSoftware EngineerCommented:
RM00101 is this the table name ? and you can only have 6 digits? Or can you have things like ABC123 ?

This will check if it is 6 digits all the time, I need to know if custnmbr is  a char or integer too.

ALTER TABLE RM00101 ADD CONSTRAINT CK_RM00101_DigitCheck CHECK (custnmbr LIKE REPLICATE ('[0-9]', 6))

This can limit the number of digits as well this will have to be more than 4 and less than 7 as an example
 ALTER TABLE RM00101 ADD CONSTRAINT CK_RM00101_DigitCheck CHECK (len(custnmbr)>3 and len(custnmbr) <7)

You can always change the CHECK constraint accordingly
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If the column should accept only numeric digits then you should define it as numeric field (INT maybe) and then the constraint should only check for numbers smaller than 1000000. Example:
ALTER TABLE RM00101  
   ADD CONSTRAINT CHK_custnmbr   
   CHECK (custnmbr < 1000000);  
GO  

Open in new window

0
Scott PletcherSenior DBACommented:
This works for any length of custnmbr from 1 to 6, verifying that the column contains only 0 thru 9:

ALTER TABLE RM00101 WITH CHECK
ADD CONSTRAINT RM00101_Check_custnmbr
CHECK(custnmbr NOT LIKE '%[^0-9]%' AND LEN(custnmbr) <= 6)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

I would need more information about the context of your business requirement and why you are looking to add a constraint to the database table, but modifying Dynamics GP tables is not standard practice and is not recommended.  

Depending on the context and your requirements, a standard and supported solution is to use Modifier & VBA to modify the UI to restrict or manage the customer IDs that are entered via the UI.  Implementing a 6 character numeric customer ID would be quite easy to do with Modifier & VBA.

If you attempt to enforce business logic via a check constraint, it can produce unhandled or unexpected SQL exceptions in Dynamics GP.  For example, I added a length check constraint on the RM00101.CUSTNMBR field, and when a long customer ID is saved, an invalid error message regarding National Accounts is displayed (see attached).

Invalid error when constraint is added to CUSTNMBR field
A SQL table constraint is not the correct approach to controlling customer ID numbering given the Dynamics GP application architecture--again, in most situations, lacking additional information about your requirements.

If you are integrating with eConnect or an integration tool, you can easily enforce the customer ID naming convention in code or the integration tool.

If you would like guidance or assistance using Modifier & VBA, please let me know.

Thanks,

Steve Endow
Microsoft MVP - Dynamics GP
0
jdr0606Author Commented:
Steve, Thanks for your comments.

I do have quite a number of VBA customizations in the version of Dynamics GP 2015 R2 that is deployed.

The challenge is that we have adopted a numeric numbering for customer numbers and anywhere we create a customer record outside normal GP the process is to increment the max customer number by 1 which has worked fine for many years.

The problem is when someone creates a new customer via the Customer Card and enters a new number incorrectly which then forces a new number max going forward. i.e. if the max customer number was 3456789 (6 digits) and someone accidentally enters 33456790 (7 digits) then the new customer numbers are 7 from that point forward. These "accidents" have happened several times and the customer number is now at 9 positions which becomes very difficult to mange with that size. In addition, if there is an alpha character entered in the number then the logic to get the max customer number fails.
0
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Thanks for the context, that helps.

If I'm understanding the situation correctly, the external processes are correctly assigning the next numeric customer ID, but it is the users in Dynamics GP that are manually entering an incorrect customer ID on the Customer Maintenance window?

If you used Modifier to make the customer ID field non-editable for most users and used VBA to query the database to get the next customer ID and populate that value in the Customer ID field, would that work?

Just to be safe, you could have the VBA fire on the Save button / Save event of the Customer Maintenance window that would get the next customer ID right before the customer is saved.  That could also allow for a brief table lock to ensure that a duplicate number isn't issued.

Let me know if that makes sense and if you think it would be viable in your environment.

Thanks,

Steve Endow
Microsoft MVP - Dynamics GP
0
jdr0606Author Commented:
Your understanding is correct.

We have used security to prevent most users from having access to the Customer Cards. For those that have access what is typically happening is that they enter a customer number to view or edit and instead of entering 34555565 they enter 345555655 or 34555565A and now we end up with a new number or one that prevents the max + 1 logic from working.

Those with access to the Customer Card have valid reasons to be in the form but i need to protect them from their own mistakes.
0
Steve EndowMicrosoft MVP - Dynamics GPCommented:
I think Modifier and/or VBA should work fine.

You could use Modifier to make the field read-only and require that users use the lookup window to retrieve existing customers.  That would avoid mistyping existing customer IDs.

If a customer needs to be manually entered, VBA could get the next ID when the new customer is saved.

And if somehow a user managed to manually enter a customer ID, you could also catch that and ensure that the customer ID value meets the numbering scheme requirements.

As a last resort, you can setup a GP Alert or SQL job that regularly checks for invalid IDs or problems with the numbering sequence and sends an email so that the problem can be fixed quickly.

Steve
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdr0606Author Commented:
Thanks

That's what I'll do
0
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Someone just told me about this product, which helps handle automatic numbering for all master records plus batch IDs.

http://www.ethotech.com/?cid=5&pid=331&ct=Products&pt=Next+Numeric+Collection


If auto numbering customers continues to be a hassle, or if you need the auto numbering in other areas of GP, it might be worth looking into.

Steve
0
jdr0606Author Commented:
Great, I'll take a look.

This is why I think this site is so good. There are individuals like yourself that use their skills and insight to help others.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.