March 8, 2012 at 1:22 am
Hi Friends,
create table pkcon (id int,name varchar(20))
-- here i'm trying to add the primary key constraint
alter table pkcon add primary key(id)
i got the following result:
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'pkcon'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Any one can tell me how can i add the primary key constraint in existing table.
Thank's
Sandeep
March 8, 2012 at 1:46 am
It is because the column that you're trying to convert to a Primary Key has been defined as allowing NULL values, which is not allowed in a PK column.
Simply alter the column beforehand to 'NOT NULL' and then create the Primary Key.
Before doing any of this you should also check that column to ensure there are no NULL values currently (or duplicate values too) in order to save you encountering any more errors related to this.
March 8, 2012 at 4:23 am
I agree with BrainDonor. Try like this.
create table pkcon (id int,name varchar(20))
-- here i'm trying to add the primary key constraint
if exists (select 1 from pkcon where id is null)
begin
print 'cant set pk as id has one or more null value'
end
else
begin
alter table pkcon alter column id int not null
alter table pkcon add primary key(id)
end
March 8, 2012 at 10:13 am
Thank u my dear it is working.
Thank's
Sandeep
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply