ALTER COLUMN?

I want to change a column in a table. The data type is int, and it allows
NULLs. I want to make it non-nullable and give it a DEFAULT value of 0. I've
looked up ALTER TABLE in BOL but I don't seem to see what the syntax is to
accomplish this. Is it even possible? TIA!You can do someting like this
CREATE TABLE TESTDEFAULTS (
ID INT)
INSERT INTO TESTDEFAULTS
VALUES (1)
SELECT *
FROM TESTDEFAULTS
ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
INSERT INTO TESTDEFAULTS
DEFAULT VALUES
SELECT *
FROM TESTDEFAULTS
--This will give an error now
INSERT INTO TestDefaultS VALUES (NULL)
DROP TABLE TESTDEFAULTS
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks! At first I got an error but once I UPDATEd the column to 0 WHERE it
IS NULL, voila! Again, thank you!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147462107.839063.66230@.d71g2000cwd.googlegroups.com...
> You can do someting like this
> CREATE TABLE TESTDEFAULTS (
> ID INT)
> INSERT INTO TESTDEFAULTS
> VALUES (1)
> SELECT *
> FROM TESTDEFAULTS
> ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
> ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
> INSERT INTO TESTDEFAULTS
> DEFAULT VALUES
>
> SELECT *
> FROM TESTDEFAULTS
> --This will give an error now
> INSERT INTO TestDefaultS VALUES (NULL)
> DROP TABLE TESTDEFAULTS
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>