Solved

Constraint on column

Posted on 2011-03-04
13
257 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
 

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Remove all trailing commas 10 72
Rebuild index results in duplicate key violation 9 42
Divide by zero error encountered. 2 35
Grid querry results 41 74
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

863 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

30 Experts available now in Live!

Get 1:1 Help Now