Solved

Constraint on column

Posted on 2011-03-04
13
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 40

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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 40

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 40

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 40

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 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