fernius fernius - 2 months ago 6
SQL Question

SQL Server 2012 - Queries working executed one on one, but not in a group

I have been trying to execute these two queries together:

ALTER TABLE afm.owned_properties_rpt_table
ALTER COLUMN bl_id CHAR(8) NOT NULL;

ALTER TABLE afm.owned_properties_rpt_table
ADD CONSTRAINT owned_properties_rpt_table_PK PRIMARY KEY (bl_id);


But I'm getting this error:


Mens. 8111, Nivel 16, Estado 1, Línea 3

Cannot define PRIMARY KEY constraint on nullable column in table
'owned_properties_rpt_table'.

Mens. 1750, Nivel 16, Estado 0, Línea 3

Could not create constraint. See previous errors.


It seems that somehow, the second line is being executed before the first one finishes.

I have tried changing semicolons by goes, using a begin transaction/commit transaction structure, and creating an auxiliary column where I copied the data in bl_id and then dropped the old column, all of them without success.

The SQL script needs to be executed on a client's server (where I can not intervene), so dividing the code is not an alternative.

I am sorry if I am missing something elementary, I have also searched for the same problem during several hours without success.

Thanks for your help.

Answer

Try this

ALTER TABLE afm.owned_properties_rpt_table ALTER COLUMN bl_id CHAR(8) NOT NULL default 'sometest';
GO
ALTER TABLE afm.owned_properties_rpt_table ADD CONSTRAINT owned_properties_rpt_table_PK PRIMARY KEY (bl_id);