CODEWITHSUNDEEP
sql-servert-sqlconstraintsalter-tablenotnull
user2688063
user2688063

Reputation: 313

Altering a sqlserver table column to add a 'Not NULL' constraint

I want to add a constraint of not null into an existing column in a table. After doing some operation of merging data. I want to alter the table to add the constraint not null. However, I do want to first verify the existance of the NotNULL constraint otherwise on the subsequesnt of running the script. The file will throw error. So what should be the way.

I want to try something like:

IF NOT EXISTs (Select..   )

BEGIN

ALTER table ....

END

Upvotes: 0

Views: 1311

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

You can use views in sys namespace for this purpose. Something like this.

select c.name,t.name,c.is_nullable 
from sys.all_columns c
inner join sys.tables t on c.object_id=t.object_id
where t.name = 'MyTable' and c.name = 'myCol';

and then alter table ... alter column...

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Maybe I do not understand this correctly, but there's no need to check for existance. You can alter a table's column to NOT NULL over and over again - if you like.

Try this:

CREATE TABLE Test(ID INT,SomeValue VARCHAR(100));
INSERT INTO Test VALUES(1,NULL),(2,'Value 2'),(NULL,'Value NULL');

--Ups, ID should not be NULL, but this throws an error 

--ALTER TABLE Test ALTER COLUMN ID INT NOT NULL;

--First have to correct the missing value

UPDATE Test SET ID=3 WHERE SomeValue='Value NULL';

--Now this works

ALTER TABLE Test ALTER COLUMN ID INT NOT NULL;

--No problem to put this statement again. No need to check for existance...

ALTER TABLE Test ALTER COLUMN ID INT NOT NULL;

--Clean-Up

GO
DROP TABLE Test;

Upvotes: 1

Related Questions