May 16, 2011 at 8:20 am
Greetings Great Experts,
This seems awfully simple but unfortunately, I am having some difficulty with it.
I have one table called Maintenance and 2 fieldnames, maintId int and MaintName nvarchar(225).
Even though I can capture this programmatically, I want to set contraints Check to ensure that maintId accepts only numbers and maintName accepts only characters.
Again, there are different ways to handle this like for instance, make maintId a primary key, not null or use my frontend app to capture this but I wanted to do using CREATE table syntax.
Here is what I have.
Create Table dbo.Maintenance
(
maintName varchar(225) NOT NULL,
maintid int NOT NULL,
CONSTRAINT Only_Characters_And_Numbers
CHECK ((maintName NOT LIKE '%[^0-9 ]%')
OR (maintid NOT LIKE '%[^A-Z ]%'))
)
GO
The table is successfully created.
However, when I insert records, it doesn't matter what I insert, it works.
For instance, this:
INSERT into Maintenance (maintId, maintName) VALUES('1', 1) inserts successfully.
It shouldn't!
What am I doing wrong?
Thanks in advance
May 16, 2011 at 8:28 am
SET IMPLICIT_TRANSACTIONS ON
GO
Create Table dbo.Maintenance
(
maintName varchar(225) NOT NULL,
maintid int NOT NULL,
CONSTRAINT Only_Characters
CHECK (maintid NOT LIKE '%[^A-Z ]%'),
CONSTRAINT Only_Numbers
CHECK (maintName NOT LIKE '%[^0-9 ]%')
)
GO
INSERT into Maintenance (maintid, maintName) VALUES('1', 1)
ROLLBACK
May 16, 2011 at 8:32 am
There's no need for a constraint on the int column.
Int columns only accept numeric data, someone tries to insert character data and you'll get a type conversion error long before the constraint would get checked.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 8:38 am
Ah, I think I read this one too fast :w00t:.
The problem you were having is that you validated both at the same time. So if either was valid, the whole row was considered valid.
Gail's right tho... use the correct datatype and you'll be fine.
May 16, 2011 at 8:45 am
Thanks to both of you for the extremely fast response.
I ran Ninja's_RGR'us's code and this is what I got:
The INSERT statement conflicted with the CHECK constraint "Only_Characters". The conflict occurred in database "DBName", table "dbo.Maintenance", column 'maintid'.
I don't have any other constraints on the table or db anywhere.
Then I followed GilaMonster's advice by using just one constraint:
SET IMPLICIT_TRANSACTIONS ON
GO
Create Table dbo.Maintenance
(
maintName varchar(225) NOT NULL,
maintid int NOT NULL,
CONSTRAINT Only_Characters
CHECK (maintName NOT LIKE '%[^0-9 ]%')
)
GO
INSERT into Maintenance (maintid, maintName) VALUES('1', 1)
ROLLBACK
This time, it worked just like it was working for me even though as you can see, maintId is an integer and the value I inserted in the INSERT statement is of character variety.
May 16, 2011 at 8:48 am
'1' can be implicitly converted to integer and inserts fine. '12K34' is not and will not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 8:58 am
Sure, I agree - that's for integer though.
What prevents 1234 from being inserted into the maintName fieldname?
That's what I am hoping the constraint is intended to do - prevent it from accepting an integer value.
May 16, 2011 at 9:03 am
simflex-897410 (5/16/2011)
Sure, I agree - that's for integer though.What prevents 1234 from being inserted into the maintName fieldname?
That's what I am hoping the constraint is intended to do - prevent it from accepting an integer value.
SET IMPLICIT_TRANSACTIONS ON
GO
Create Table dbo.Maintenance
(
maintName varchar(225) NOT NULL,
maintid int NOT NULL,
CONSTRAINT Only_Characters
CHECK (maintName LIKE '%[A-Z ]%')
)
GO
INSERT into dbo.Maintenance (maintName, maintid) VALUES('abcdefghijklmnopqrstuvwxyz', 1)
INSERT into dbo.Maintenance (maintName, maintid) VALUES('1', 1)
SELECT * FROM dbo.Maintenance
ROLLBACK
May 16, 2011 at 9:05 am
You have a double negative problem.
Your constraint currently reads Where column NOT like NON-Numeric characters, ie must be like numeric characters only. The ^ is the second negation.
Maybe this?
Create Table dbo.Maintenance
(
maintName varchar(225) NOT NULL,
maintid int NOT NULL,
CONSTRAINT Only_Characters
CHECK (maintName NOT LIKE '%[0-9 ]%')
)
GO
Forbid the characters 0-9 and ' ', allow anything else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 9:07 am
Ninja's_RGR'us (5/16/2011)
SET IMPLICIT_TRANSACTIONS ONGO
Create Table dbo.Maintenance
(
maintName varchar(225) NOT NULL,
maintid int NOT NULL,
CONSTRAINT Only_Characters
CHECK (maintName LIKE '%[A-Z ]%')
)
GO
INSERT into dbo.Maintenance (maintName, maintid) VALUES('abcdefghijklmnopqrstuvwxyz', 1)
INSERT into dbo.Maintenance (maintName, maintid) VALUES('1', 1)
SELECT * FROM dbo.Maintenance
ROLLBACK
INSERT into Maintenance (maintid, maintName) VALUES('1', 'abcdefghij0lm')
go
INSERT into Maintenance (maintid, maintName) VALUES('1', '0123456789Z')
go
Your constraint requires one non-numeric character, but allows the rest of the string to be anything
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 9:11 am
it's quite interesting as to what sometimes seems quite simple often isn't.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 16, 2011 at 9:20 am
colin.Leversuch-Roberts (5/16/2011)
it's quite interesting as to what sometimes seems quite simple often isn't.
Yup... and I'm blanking out now... only remember the only digits functions and yet I'm feeling it can't be applied here even with A-Z...
:ermm:
May 16, 2011 at 9:27 am
colin.Leversuch-Roberts (5/16/2011)
it's quite interesting as to what sometimes seems quite simple often isn't.
Yeah, this is a Hard problem with SQL functions. With regex it would be trivial, but...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 9:31 am
You are right, I think it works now.
If I try this:
INSERT INTO maintenance(maintid, maintName) values(1, '12345')
It doesn't insert - the constraint violation error is raised.
The INSERT statement conflicted with the CHECK constraint "Only_Characters". The conflict occurred in database "DBName", table "dbo.maintenamce", column 'maintname'.
if, however, I try with atleast one non-numeric character, it works like you stated.
Can I grab that error and make it human readable like instead of the error above, I have something like, maintname must containe non-numeric value
Is that possible?
Thanks very, very much to you guys for your prompt responses and patience.
May 16, 2011 at 9:33 am
simflex-897410 (5/16/2011)
You are right, I think it works now.If I try this:
INSERT INTO maintenance(maintid, maintName) values(1, '12345')
It doesn't insert - the constraint violation error is raised.
The INSERT statement conflicted with the CHECK constraint "Only_Characters". The conflict occurred in database "DBName", table "dbo.maintenamce", column 'maintname'.
if, however, I try with atleast one non-numeric character, it works like you stated.
Can I grab that error and make it human readable like instead of the error above, I have something like, maintname must containe non-numeric value
Is that possible?
Thanks very, very much to you guys for your prompt responses and patience.
Yes, use try catch either in the procedure or in you application.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply