CODEWITHSUNDEEP
sql-serversql-server-2005
Vinod
Vinod

Reputation: 32861

Set a existing column of MS SQL table as NOT NULL

How to Set a existing column of MS SQL table as NOT NULL?

Upvotes: 48

Views: 60501

Answers (2)

Adam Ralph
Adam Ralph

Reputation: 29956

ALTER TABLE tablename
ALTER COLUMN columnname datatype NOT NULL

You will obviously have to make sure that the column does not contain any NULL values before doing this.

E.g.

ALTER TABLE orders
ALTER COLUMN customer_id INT NOT NULL

Upvotes: 90

Richard Harrison
Richard Harrison

Reputation: 19393

Firstly ensure that the fields have non null values. In this case I'm working with a field that has a GUID nvarchar so I'll do

UPDATE tablename 
SET    fieldname = Newid() 
WHERE  fieldname IS NULL;

Then as Adam Ralph says

ALTER TABLE tablename ALTER COLUMN fieldname datatype NOT NULL

Upvotes: 12

Related Questions