Alter Table problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Alter Table problem

Author

Topic

mdanwerali
Starting Member

30 Posts

Posted - 2002-12-04 : 03:11:37

Hi,

I have already 50 tables with lots of data in it, now i got a new requirement for adding Default Constraint to few tables.

When i am trying this using

ALTER TABLE ALTER COLUMN ADD CONSTRAINTS..

then it is gining error, then i read Sql Server help in that, they have not mentioned how to add Default Constraint to an existing Column, but we can add while adding a new column.

but what i want is without data lost i was to add default constraint to my table.

plz help

Anwer

mr_mist
Grunnio

1870 Posts

Posted - 2002-12-04 : 03:22:16

Your syntax is wrong. From BOL Alter Table :

quote:

D. Alter a table to add an unverified constraint
This example adds a constraint to an existing column in the table. The column has a value that violates the constraint; therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow the constraint to be added.

CREATE TABLE doc_exd ( column_a INT)
GO
INSERT INTO doc_exd VALUES (-1)
GO
ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1)
GO
EXEC sp_help doc_exd
GO
DROP TABLE doc_exd
GO