CODEWITHSUNDEEP
sql-serversql-server-2005
user2431727
user2431727

Reputation: 907

How Can I ALTER COLUMN to NOT NULL and ADD PRIMARY KEY in the same SQL

I have a column my_id which is already set as varchar(50) NULL. Now I want to alter it to NOT NULL and add it as primary key for that table. Since there is no other primary keys yet. I have written like below.

ALTER TABLE my_details
ALTER COLUMN my_id varchar(50) NOT NULL;

ALTER TABLE my_details
ADD PRIMARY KEY (my_id);

But I get an error:

Cannot define PRIMARY KEY constraint on nullable column in table 'my_details'. Could not create constraint. See previous errors.

How to write a single query for this?

Upvotes: 5

Views: 38353

Answers (4)

Gaurav Joshi
Gaurav Joshi

Reputation: 1001

1 step : make your column not null query-> alter table employees alter column emp_id integer NOT NULL;

2 step : now after updating column add primary key into that column query - > alter table employees ADD PRIMARY KEY (emp_id);

Upvotes: 0

Elaskanator
Elaskanator

Reputation: 1216

Using GO is not always an option, such as inside a stored procedure.

The workaround for me was to use dynamic SQL to bypass the excessive, premature schema validation:

create table #test(ID int not null, ForeignKeyID int null)
alter table #test alter column ForeignKeyID int not null
exec ('alter table #test add primary key(ID, ForeignKeyID)')

Of course, as Cool_Br33ze mentioned, you need to ensure there are no NULLs in the column(s) you are updating beforehand.

P.S. I am unable to directly reproduce the error, but have seen it occurring (on PROD unfortunately).

Upvotes: 5

Mazhar
Mazhar

Reputation: 3837

if this is an existing table with nulls in the my_id column then you have to update the column to remove the nulls first

CREATE TABLE my_details ( my_id varchar(50) NULL )
GO

INSERT INTO dbo.my_details ( my_id ) VALUES (NULL  )
GO

ALTER TABLE my_details
ALTER COLUMN my_id varchar(50) NOT NULL;
GO

Gives you this error

Msg 515, Level 16, State 2, Line 9 Cannot insert the value NULL into column 'my_id', table 'xStuff.dbo.my_details'; column does not allow nulls. UPDATE fails.

trying to add a primary key gives you this

ALTER TABLE my_details
ADD PRIMARY KEY (my_id); 
GO

Msg 8111, Level 16, State 1, Line 16 Cannot define PRIMARY KEY constraint on nullable column in table 'my_details'. Msg 1750, Level 16, State 0, Line 16 Could not create constraint. See previous errors.

However

place an update in between and this change works

CREATE TABLE my_details ( my_id varchar(50) NULL );
GO

INSERT INTO dbo.my_details ( my_id ) VALUES (NULL  );
GO

UPDATE dbo.my_details SET my_id = '' WHERE my_id IS NULL;
GO

ALTER TABLE my_details
ALTER COLUMN my_id varchar(50) NOT NULL;
GO

ALTER TABLE my_details
ADD PRIMARY KEY (my_id); 
GO

Upvotes: 0

R.P Silveira
R.P Silveira

Reputation: 402

Just place a GO between two commands, to execute them separately:

ALTER TABLE my_details
ALTER COLUMN my_id varchar(50) NOT NULL;

GO

ALTER TABLE my_details
ADD PRIMARY KEY (my_id);

Upvotes: 8

Related Questions