473,382 Members | 1,639 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

Problem with Check Constraints

RAD
I am working with an evaluation copy of SQL Server 2000 for the first
time; my DB experience lies with MS Access.

I have a simple table in SQL Server (tblCompany) that has a field
called "Ticker." When new company stock tickers (i.e., MSFT for
Microsoft) are entered into the field, I'd like them in all
caps--whether the user types msft, Msft, MsFt, etc. In Access, this
was easy--simply set the Format to ">" in table design view.

In SQL Server Design Table view, I've clicked on "Manage Constraints"
and put the following code in that I found elsewhere:

([Ticker] = upper([Ticker]))

I then checked all three boxes below: "Check existing data on
creation," "Enforce constraint for replication," and "Enforce
constraint for INSERTs and UPDATEs." The first one, "Check existing
data..." is checked as I've already entered in some data in the field
in lowercase to see if the check constraint would go back and change
it to Upper Case--this because I'm wanting to ultimately migrate a
table from Access to SQL Server and ensure that all Tickers are in
Upper Case.

I'm able to do this and then save the table design with changes; but
every time, I then go and look at the table data to see if the check
constraint was applied, and each time it is not; then, I go back to
"Manage Constraints" and find that the "Check existing data..." box is
unchecked. I've gone through this SEVERAL times.

Hoping this is something simple. Apologize for my "newbieness." I've
got a "For Dummies" book in front of me as well as numerous Internet
windows open, trying to figure this out. Have checked books online on
the MSFT site as well to no avail.

Thanks in advance--

RAD
Jul 20 '05 #1
3 9022
A constraint enforces data integrity rules but does not change existing or
newly inserted data. You need to cleanup your data and then add the
constraint to only permit uppercase values going forward.

If you want to automatically change values to upper case as they are entered
on the server side, you'll need to do this in a trigger. IMHO, this task is
better done in application code and let the database just enforce the data
integrity rule.

I don't know the details of the constraint you are adding but be aware that
case sensitivity is determined by collations in SQL 2000. The default
collation is case insensitive so you'll need to override the default
case-insensitive compare in your constraint. The example script below will
correct existing data and add a check constraint to ensure only upper case
values are allowed.

UPDATE Company
SET Ticker = UPPER('Ticker')
GO

ALTER TABLE Company
ADD CONSTRAINT CK_Ticker
CHECK (Ticker COLLATE SQL_Latin1_General_Cp1_CS_AS = UPPER(Ticker))
GO

On a side note, be aware that Hungarian notation (e.g. 'tbl' prefixes) are
frowned upon in client-server database design. The underlying database
implementation (table or view) should be transparent to database users.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"RAD" <rd********@nyc.rr.com> wrote in message
news:d4*************************@posting.google.co m...
I am working with an evaluation copy of SQL Server 2000 for the first
time; my DB experience lies with MS Access.

I have a simple table in SQL Server (tblCompany) that has a field
called "Ticker." When new company stock tickers (i.e., MSFT for
Microsoft) are entered into the field, I'd like them in all
caps--whether the user types msft, Msft, MsFt, etc. In Access, this
was easy--simply set the Format to ">" in table design view.

In SQL Server Design Table view, I've clicked on "Manage Constraints"
and put the following code in that I found elsewhere:

([Ticker] = upper([Ticker]))

I then checked all three boxes below: "Check existing data on
creation," "Enforce constraint for replication," and "Enforce
constraint for INSERTs and UPDATEs." The first one, "Check existing
data..." is checked as I've already entered in some data in the field
in lowercase to see if the check constraint would go back and change
it to Upper Case--this because I'm wanting to ultimately migrate a
table from Access to SQL Server and ensure that all Tickers are in
Upper Case.

I'm able to do this and then save the table design with changes; but
every time, I then go and look at the table data to see if the check
constraint was applied, and each time it is not; then, I go back to
"Manage Constraints" and find that the "Check existing data..." box is
unchecked. I've gone through this SEVERAL times.

Hoping this is something simple. Apologize for my "newbieness." I've
got a "For Dummies" book in front of me as well as numerous Internet
windows open, trying to figure this out. Have checked books online on
the MSFT site as well to no avail.

Thanks in advance--

RAD

Jul 20 '05 #2
On 25 Jan 2004 10:17:18 -0800, rd********@nyc.rr.com (RAD) wrote:
I am working with an evaluation copy of SQL Server 2000 for the first
time; my DB experience lies with MS Access.

I have a simple table in SQL Server (tblCompany) that has a field
called "Ticker." When new company stock tickers (i.e., MSFT for
Microsoft) are entered into the field, I'd like them in all
caps--whether the user types msft, Msft, MsFt, etc. In Access, this
was easy--simply set the Format to ">" in table design view.

In SQL Server Design Table view, I've clicked on "Manage Constraints"
and put the following code in that I found elsewhere:

([Ticker] = upper([Ticker]))

I then checked all three boxes below: "Check existing data on
creation," "Enforce constraint for replication," and "Enforce
constraint for INSERTs and UPDATEs." The first one, "Check existing
data..." is checked as I've already entered in some data in the field
in lowercase to see if the check constraint would go back and change
it to Upper Case--this because I'm wanting to ultimately migrate a
table from Access to SQL Server and ensure that all Tickers are in
Upper Case.

I'm able to do this and then save the table design with changes; but
every time, I then go and look at the table data to see if the check
constraint was applied, and each time it is not; then, I go back to
"Manage Constraints" and find that the "Check existing data..." box is
unchecked. I've gone through this SEVERAL times.

Hoping this is something simple. Apologize for my "newbieness." I've
got a "For Dummies" book in front of me as well as numerous Internet
windows open, trying to figure this out. Have checked books online on
the MSFT site as well to no avail.

Thanks in advance--

RAD

That doesn't work, as can be shown with

select ticker from tblCompany where ticker = 'MSFT'

Your row will be returned regardless of the case of the data.

this may be something that can be set at the database level,
alternatively use a trigger to uppercase the data.

Something like;

create trigger instblCompany
on tblCompany
instead of insert
as
insert into tblCompany
select upper(ticker), all other columns
from inserted
Jul 20 '05 #3
RAD
Thanks both Dan and Lyndon--I'll give it a whirl.

RAD
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Doug Baroter | last post by:
Hi, DDLs and DMLs: create table #job (jobID int identity(1,1) primary key, jobName varchar(25) unique not null, jobEndDate dateTime, jobComplete bit default(0), check (( is null and = 0) OR (...
0
by: Yuraukar | last post by:
I would like to check XML files with respect to identity constraints specified in the Schema (key, unique, keyref). Is there any free parser/tool that does this? I have worked with XML Spy 4.4, but...
4
by: Tom Jastrzebski | last post by:
Hello everybody, Here is the problem I came across experimenting with Generics. I would like to write a class or a struct adding integer to any other, initially undefined *numeric type*. So, my...
9
by: Edmund Dengler | last post by:
Greetings! Just trying some tests out, and wanted to know about some optimizations. If I do a CHECK constraint on a table, is this used to optimize a SELECT or does Postgresql rely mostly on...
6
by: sarada7 | last post by:
How to check if DB Constraints are enabled in a database?
3
by: ferg | last post by:
I have a Customer table. The table has two different CHECK constraints. Then there is the Customer details dialog, which provides the user with an UI for changing users. I have some UPDATE sql,...
1
by: huyuhui | last post by:
The following is a question of LOAD utility. Question: How does the DB2 enforce table check constraints for data added to table with the LOAD utility? A. With the BUILD phase of LOAD B. With the...
1
by: PaulR | last post by:
Hi, (DB2 LUW 8.2) Is the DB2 optimiser able to use check constraints ? e.g table1 ( name varchar(30)
1
by: Dan Holmes | last post by:
How do you connect the check constraints on the Table object with the columns in the table? I ended up doing this and it smells bad. foreach (Column cn in t.Columns) { foreach (Check ch in...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.