adding primary key to an existing table

Hi all,

I there a way to check if the column is set to NOT NULL is equal to true, if not set to true i have to set it to true before adding my primary key or else i'll get an error message.

in my SP i have a ALTER TABLE table1 ADD PRIMARY KEY (colum1)

My question is, how can i check if the column is set to not null is false and how can i update to set it true?

Thanks.

Teo,

You can use the DMVs sys.tables and sys.columns to determine if a column is nullable.

select t.name, c.name, c.is_nullable
from sys.tables t join sys.columns c
on t.object_id = c.object_id
where t.name = 'fact'

Secondly, to change a column to NOT NULL if it is, use ALTER TABLE ... ALTER COLUMN.

ALTER TABLE dbo.fact ALTER COLUMN dim1_id INT NOT NULL

Regards,

Galex

|||

Why you want to check for NULL. without checking it just set it to not null

ALTER TABLE table1 alter column col1 int not null

ALTER TABLE table1 ADD PRIMARY KEY (col1)

it will not retun any errors even it is already set to not null