Tags: accept, amendments, attribute, attributes, created, database, integers, microsoft, mysql, oracle, server, sql, table, zero
Greater than zero
On Database » Microsoft SQL Server
4,506 words with 9 Comments; publish: Wed, 07 May 2008 12:04:00 GMT; (25062.50, « »)
Hi all,
I have created a table and I need to make amendments to one of the attribute
s. The attribute is set to accept integers but now I have to change to accep
t only integers greater than zero. May I know how do I make this change?
Thanks
http://sqlserver.itags.org/q_sql-server_120559.html
All Comments
Leave a comment...
- 9 Comments
- You can create a check constrint for that columns
ALTER TABLE urTable WITH NOCHECK -- puting nocheck option will disable
checking of existing data
ADD CONSTRAINT urConstraintName CHECK ( urColumnName > 0)
lara
"Eric_Singapore" <Eric_Singapore.255nxr.sqlserver.itags.org.mail.codecomments.com> wrote in
message news:Eric_Singapore.255nxr.sqlserver.itags.org.mail.codecomments.com...
> Hi all,
> I have created a table and I need to make amendments to one of the
> attributes. The attribute is set to accept integers but now I have to
> change to accept only integers greater than zero. May I know how do I
> make this change?
>
> Thanks
>
> --
> Eric_Singapore
> ---
> Posted via http://www.codecomments.com
> ---
>
#1; Wed, 07 May 2008 12:05:00 GMT
- Add a check constraint:
alter table <table name>
add constraint <constrait name>
check (<column name> > 0)
go
ML
#2; Wed, 07 May 2008 12:06:00 GMT
- I always make it a practice to NAME constaints so I might be able to tell
what the deal is from the constraint name... So I'd make a little change to
lara's suggestion
ALTER TABLE urTable WITH NOCHECK -- puting nocheck option will disable
checking of existing data
ADD CONSTRAINT ValueMustBeGreaterThanZero CHECK ( urColumnName > 0)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"lara169" wrote:
> You can create a check constrint for that columns
> ALTER TABLE urTable WITH NOCHECK -- puting nocheck option will disable
> checking of existing data
> ADD CONSTRAINT urConstraintName CHECK ( urColumnName > 0)
> lara
> "Eric_Singapore" <Eric_Singapore.255nxr.sqlserver.itags.org.mail.codecomments.com> wrote in
> message news:Eric_Singapore.255nxr.sqlserver.itags.org.mail.codecomments.com...
>
>
#3; Wed, 07 May 2008 12:07:00 GMT
- The NOCHECK option, personally, makes no sense to me - "enforce a rule but
not just yet"? IMHO existing values should be checked (and repaired if
needed) before a constraint is added.
But I could be wrong.
ML
#4; Wed, 07 May 2008 12:08:00 GMT
- "ML" <ML.sqlserver.itags.org.discussions.microsoft.com> wrote in message
news:BCF85086-F4BE-4FDB-906D-F95AEAF9F166.sqlserver.itags.org.microsoft.com...
> The NOCHECK option, personally, makes no sense to me - "enforce a rule but
> not just yet"? IMHO existing values should be checked (and repaired if
> needed) before a constraint is added.
> But I could be wrong.
>
> ML
>
I've always wondered why that option was offered.
Glad to see that I'm not alone.
#5; Wed, 07 May 2008 12:09:00 GMT
- Yeah, imagine using that option when picking up your kids from school.
ML
#6; Wed, 07 May 2008 12:10:00 GMT
- "ML" <ML.sqlserver.itags.org.discussions.microsoft.com> wrote in message
news:850594CF-BD71-437B-97FE-256143D2C93A.sqlserver.itags.org.microsoft.com...
> Yeah, imagine using that option when picking up your kids from school.
>
> ML
You have a STRANGE sense of humour ML.
Or is it because it's Friday and it's early in your neck of the woods.
Have a coffee... or two.
#7; Wed, 07 May 2008 12:11:00 GMT
- It's 16:58 over here. As far as coffee is concerned in deed I need a re-fill
,
but that doesn't change the fact that coming home with the wrong set of
children just isn't good practice.
:)
ML
#8; Wed, 07 May 2008 12:12:00 GMT
- At what point have I lost you? I guess it was a bad example...
NOCHECK in SQL = existing data is not checked.
NOCHECK in real life = criminal act.
ML
#9; Wed, 07 May 2008 12:13:00 GMT