altering table

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?
1jawsAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
1jawsAuthor Commented:
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
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
1jawsAuthor Commented:
thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.