Solved

Constraint on column

Posted on 2011-03-04
13
254 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:algotube
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:algotube
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

Thanks again Icohan
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks again for your time and help very much appreciated.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now