Solved

Constraint on column

Posted on 2011-03-04
13
258 Views
Last Modified: 2012-05-11
Hello experts I have a database called BadgeDB which has a table dbo.ContractorBadges with a Column of

Name         varchar(255)

The Name field is currently a free form text field. The purpose of this field is to accept both the first and last name of a Contractor.  If only one name is entered instead of first and last our import will fail as it will leave the “last name” within our database blank resulting in the data not coming across our interface.

I would like to put a constraint on this field to ensure that both first and last name are entered – example at least one blank space – is input therefore creating two words or names in the field.  Further enhancement is to create another constraint  to disallow and symbols from being entered in the field.  Symbols such as ! @ # $ % ^ & * ( ) - ? < > / should not be accepted

How would I go about writing both or a single constraints for doing this?

Thank you so much for your time.
0
Comment
Question by:algotube
13 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35039092
alter table <tbl> add constraint <constraintname> check (charindex(' ', [Name]) > 0 and charindex('!', [Name]) = 0 and ... )

0
 
LVL 1

Assisted Solution

by:babytam
babytam earned 50 total points
ID: 35039573
These kind of constraints should be enforced on the GUI.  You should have two fields on the GUI, First Name and Last Name fields.  When you write it back to the table, you can concatenate the fields together.  This will ensure you have consistent data format.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 35040325
It should be something like below and keep in mind that you would have to fix all data before adding the constraint if you would want to add it with check:

http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx

use BadgeDB
go
 
ALTER TABLE dbo.ContractorBadges WITH NOCHECK
ADD CONSTRAINT Name CHECK (name like " " AND name NOT like "[!@#$%^&*( )-?< >/]")

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:algotube
ID: 35057812
Thanks everyone for your replies.

Icohan

I am using your example:

use BadgeDB
go
 
ALTER TABLE dbo.ContractorBadges WITH NOCHECK
ADD CONSTRAINT CK_Name CHECK (Name like " " AND Name NOT like "[!@#$%^&*( )-?< >/]")


and I keep on getting:

Msg 207, Level 16, State 1, Line 3
Invalid column name ' '.
Msg 207, Level 16, State 1, Line 3
Invalid column name '[!@#$%^&*( )-?< >/]'.

I checked again and the column is Name (varchar(255)......what am I missing here.........???

Thank you
0
 

Author Comment

by:algotube
ID: 35058711
Also, tried

use BadgeDB
go
 
ALTER TABLE dbo.ContractorBadges WITH NOCHECK
ADD CONSTRAINT Name CHECK (Name like " " AND Name NOT like "[!@#$%^&*( )-?< >/]")

stell getting:

Msg 207, Level 16, State 1, Line 4
Invalid column name ' '.
Msg 207, Level 16, State 1, Line 4
Invalid column name '[!@#$%^&*( )-?< >/]'.
0
 
LVL 39

Accepted Solution

by:
lcohan earned 450 total points
ID: 35058776
Sorry...no double quotes, please replace the command like below with singlequotes then test it with an insert - first will fail with constraint violation as expected, second works as it has a space in it:

use BadgeDB
go

create table test   (name varchar(255) null)
go

--ALTER TABLE test  drop CONSTRAINT CK_Name
ALTER TABLE test   WITH NOCHECK ADD CONSTRAINT CK_Name CHECK (Name like '% %' AND Name NOT like '[!@#$%^&*( )-?< >/]')

insert into test   (name) select '*'
insert into test   (name) select 'first last'

drop table test
0
 

Author Comment

by:algotube
ID: 35059718
lcohan

   Thank you so much for your help very much appreciated.

The first part worked like a charm we entered -- example

Sam -- failed with constraint violation
Sam Preston -- worked

when we entered:

Sam # or Sam @123  or Sam &Preston -- this still worked were it should have given us a failer because of the AND Name NOT like '[!@#$%^&*( )-?< >/]') condition

Sorry, with I could award more points............ for you....... do I need a comma between the symbals or something????
0
 

Author Comment

by:algotube
ID: 35059769
Sorry, with I could award more points............ for you....... do I need a comma between the symbals or something????

should read

Sorry, wish I could award more points............ for you....... do I need a comma between the symbals or something????
0
 
LVL 39

Expert Comment

by:lcohan
ID: 35059771
That's hlf true because if you try

insert into test   (name) select 'sam*' you get the error
insert into test   (name) select 'sam *' indeed it works and let me try diffrenet constraint
0
 

Author Comment

by:algotube
ID: 35059988

Thanks again Icohan
0
 
LVL 39

Expert Comment

by:lcohan
ID: 35060011
Well, I tried all posible combinations I can think of however can't find a combination to suit all you need by using only one column so I suggest you use two columns like first_name, last_name and put one constraint on each column to NOT allow space and any of the chars you listed above.
0
 

Author Comment

by:algotube
ID: 35060210
Sounds like a plan, babytam also suggested this and I believe it's the way to go.  I will explain this to my developers and get back to you. Thanks again for looking into this.
0
 

Author Closing Comment

by:algotube
ID: 35072613
Thanks again for your time and help very much appreciated.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question