4

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2431727
  • 877
  • 2
  • 15
  • 46
  • Possible duplicate of [Add primary key to existing table](http://stackoverflow.com/questions/11794659/add-primary-key-to-existing-table) – Ullas Mar 31 '17 at 05:52
  • 1
    You cannot do this in a single query - you need the two steps - first convert column to be `NOT NULL`, and in a second step, add the primary key constraint to the table with that newly non-nullable column – marc_s Mar 31 '17 at 05:57

4 Answers4

7

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);
R.P Silveira
  • 372
  • 2
  • 10
3

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

Elaskanator
  • 1,135
  • 10
  • 28
0

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
Mazhar
  • 3,797
  • 1
  • 12
  • 29
0

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

Gaurav Joshi
  • 861
  • 1
  • 11
  • 17