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
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;
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...
