|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Modifying table issue
Hi
When I try to modify a Bit field's Allow Null to No, I get the message that change can not be made without dropping the table. The table contains data. How can I make this change while maintaining the data in the table? Thanks Regards "John" <info@nospam.infovis.co.uk> wrote in message If you do not have any NULL values in the table, then you can simply do the news:u0Ws7DP9JHA.5064@TK2MSFTNGP03.phx.gbl... > Hi > > When I try to modify a Bit field's Allow Null to No, I get the message > that change can not be made without dropping the table. The table contains > data. How can I make this change while maintaining the data in the table? > > Thanks > > Regards > > following: ALTER TABLE <tablename> ALTER COLUMN <bit column name> bit NOT NULL If you do have nulls already in the table, then you must either update the null values to 0 or 1 first then run the code above. Your other option is to create a CHECK CONSTRAINT as in this example. ALTER TABLE <tablename> WITH NOCHECK ADD CONSTRAINT <constraint name> CHECK (<bit column name> IS NOT NULL) I hope this helps. Rick Sawtell MCT, MCSD, MCDBA, MCITPro Hi
When ALTER Command is used it works fine. However when using the graphic designer I get the error. Strange. Thanks Regards Show quoteHide quote "Rick Sawtell" <r_sawtell@nospam.hotmail.com> wrote in message news:%230omsSP9JHA.1488@TK2MSFTNGP03.phx.gbl... > > "John" <info@nospam.infovis.co.uk> wrote in message > news:u0Ws7DP9JHA.5064@TK2MSFTNGP03.phx.gbl... >> Hi >> >> When I try to modify a Bit field's Allow Null to No, I get the message >> that change can not be made without dropping the table. The table >> contains data. How can I make this change while maintaining the data in >> the table? >> >> Thanks >> >> Regards >> >> > > If you do not have any NULL values in the table, then you can simply do > the following: > > ALTER TABLE <tablename> ALTER COLUMN <bit column name> bit NOT NULL > > > If you do have nulls already in the table, then you must either update the > null values to 0 or 1 first then run the code above. > > > Your other option is to create a CHECK CONSTRAINT as in this example. > > > ALTER TABLE <tablename> WITH NOCHECK ADD CONSTRAINT <constraint name> > CHECK (<bit column name> IS NOT NULL) > > > I hope this helps. > > Rick Sawtell > MCT, MCSD, MCDBA, MCITPro > > > > By default, the tool will not allow things where the tool thinks it
need to re-create the table (not that you are smarter than the tool since you know this can be achieved using an ALTER TABLE). You can configure somewhere in Tools, Option to allow for this. Note, though, that often the tool isn't as smart as we are (i.e., it does things in clumsy ways) - so always watch that change script before saving. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "John" <info@nospam.infovis.co.uk> wrote in message news:uyGFqaP9JHA.3836@TK2MSFTNGP02.phx.gbl... > Hi > > When ALTER Command is used it works fine. However when using the > graphic designer I get the error. Strange. > > Thanks > > Regards > > "Rick Sawtell" <r_sawtell@nospam.hotmail.com> wrote in message > news:%230omsSP9JHA.1488@TK2MSFTNGP03.phx.gbl... >> >> "John" <info@nospam.infovis.co.uk> wrote in message >> news:u0Ws7DP9JHA.5064@TK2MSFTNGP03.phx.gbl... >>> Hi >>> >>> When I try to modify a Bit field's Allow Null to No, I get the >>> message that change can not be made without dropping the table. >>> The table contains data. How can I make this change while >>> maintaining the data in the table? >>> >>> Thanks >>> >>> Regards >>> >>> >> >> If you do not have any NULL values in the table, then you can >> simply do the following: >> >> ALTER TABLE <tablename> ALTER COLUMN <bit column name> bit NOT NULL >> >> >> If you do have nulls already in the table, then you must either >> update the null values to 0 or 1 first then run the code above. >> >> >> Your other option is to create a CHECK CONSTRAINT as in this >> example. >> >> >> ALTER TABLE <tablename> WITH NOCHECK ADD CONSTRAINT <constraint >> name> CHECK (<bit column name> IS NOT NULL) >> >> >> I hope this helps. >> >> Rick Sawtell >> MCT, MCSD, MCDBA, MCITPro >> >> >> >> > >  
Other interesting topics
32 bit SQL Server on 64 bit OS
SQL Timeout Error how to fix error "Connection is busy with results for another hstmt" Sql server 2000 client libraries and SQL Server 2008 refer to db table dbcc checkdb fix Access 2007 Import Wizard Import SQL Data to Word/Export Data from Word to SQL? Error with no reference Use view or sp? |
|||||||||||||||||||||||
