Solved

altering table

Posted on 2011-09-19
4
240 Views
Last Modified: 2012-05-12
I want to alter table's column's (x) to change to datatype to bit and set default to 'false'  how can I do that?
0
Comment
Question by:1jaws
  • 2
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36564075
1jaws,

You can use, ALTER TABLE ALTER COLUMN and ADD CONSTRAINT.
BOL: http://msdn.microsoft.com/en-us/library/ms190273.aspx

For example, if your column is called Flag1 in the Table1 table:
ALTER TABLE Table1 
ALTER COLUMN Flag1 BIT NOT NULL;
ALTER TABLE Table1 
ADD CONSTRAINT Flag1Dflt 
DEFAULT 0 FOR Flag1;

Open in new window


If you have existing data that fits BIT data type, use WITH VALUES:
ALTER TABLE Table1 
ALTER COLUMN Flag1 BIT NOT NULL;
ALTER TABLE Table1 
ADD CONSTRAINT Flag1Dflt 
DEFAULT 0 FOR Flag1 WITH VALUES;

Open in new window


Kevin
0
 

Author Comment

by:1jaws
ID: 36564226
I am doing second one and it is giving me error saying cannot insert null into tbl1... by the way what is Flag1Dflt means?
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 36564245
Sorry. I plugged in NOT NULL. If field has NULLs currently and NULLs are allowed then that should be just NULL. Flat1Dflt is name of the CONSTRAINT. You can name that what you want. If you do not want NULLs, then before doing the ALTER statement:

UPDATE Table1 SET Flag1 = 0
WHERE Flag1 IS NULL;

Open in new window

0
 

Author Closing Comment

by:1jaws
ID: 36564250
thank you!!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question