Solved

altering table

Posted on 2011-09-19
4
236 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now