|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Greater than zeroHi 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 ------------------------------------------------------------------------ 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 Show quote "Eric_Singapore" <Eric_Singapore.255***@mail.codecomments.com> wrote in message news:Eric_Singapore.255nxr@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 > ------------------------------------------------------------------------ > 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) -- Show quoteWayne 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.255***@mail.codecomments.com> wrote in > message news:Eric_Singapore.255nxr@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 > > ------------------------------------------------------------------------ > > > > > 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 --- http://milambda.blogspot.com/ "ML" <M*@discussions.microsoft.com> wrote in message I've always wondered why that option was offered.news:BCF85086-F4BE-4FDB-906D-F95AEAF9F166@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 > Glad to see that I'm not alone. Yeah, imagine using that option when picking up your kids from school.
ML --- http://milambda.blogspot.com/ "ML" <M*@discussions.microsoft.com> wrote in message You have a STRANGE sense of humour ML.news:850594CF-BD71-437B-97FE-256143D2C93A@microsoft.com... > Yeah, imagine using that option when picking up your kids from school. > > > ML Or is it because it's Friday and it's early in your neck of the woods. Have a coffee... or two. 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--- http://milambda.blogspot.com/ "ML" <M*@discussions.microsoft.com> wrote in message Practice for what???news:A2214788-E91D-4E18-98E7-4175142CD0D8@microsoft.com... > 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 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 --- http://milambda.blogspot.com/ "ML" <M*@discussions.microsoft.com> wrote in message I was just trying to prove that there may be stranger "senses of humour" news:FFA03BC3-F6EE-47A3-A70C-1D7334D6138D@microsoft.com... > 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 than yours. :-) ROFL! :D
Well, you've succeeded. I think I've just dislocated my pancreas laughing. ML --- http://milambda.blogspot.com/ > I've always wondered why that option was offered. How about if you want to extend the domain of allowable values (like used to allow 0-10, now allows 0-15)? If you are alone while removing the old and adding the new constraint, you know that no rows violates the new constraint. And, assume some 200 million rows in the table. :-) -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message news:OO$Hwc1TGHA.5884@TK2MSFTNGP14.phx.gbl... > "ML" <M*@discussions.microsoft.com> wrote in message > news:BCF85086-F4BE-4FDB-906D-F95AEAF9F166@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. > Very good point Tibor.
I'll go to bed a bit less dense tonight. Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:%23BFB3V3TGHA.2276@tk2msftngp13.phx.gbl... >> I've always wondered why that option was offered. > > How about if you want to extend the domain of allowable values (like used > to allow 0-10, now allows 0-15)? If you are alone while removing the old > and adding the new constraint, you know that no rows violates the new > constraint. And, assume some 200 million rows in the table. :-) > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message > news:OO$Hwc1TGHA.5884@TK2MSFTNGP14.phx.gbl... >> "ML" <M*@discussions.microsoft.com> wrote in message >> news:BCF85086-F4BE-4FDB-906D-F95AEAF9F166@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. >> > Yes, this does make sense - extending the constraint. Good to know, thanks.
ML --- http://milambda.blogspot.com/ Add a check constraint:
alter table <table name> add constraint <constrait name> check (<column name> > 0) go ML --- http://milambda.blogspot.com/  |
|||||||||||||||||||||||
Other interesting topics