March 8, 2012 at 10:45 pm
Hi Friends,
I'm trying to adding the primarey key on table column. table having 7 numbers of rows,
Any one can tell me how to primarey key on table.
EX:
create table index1 (eno int,ename varchar(20),sal money)
insert index1 values(105,'ajay',9000),(103,'akil',8000),(101,'ajay',7000),(102,'ram',8500),(104,'vinad',9200)
-- now i'm trying to give not null constraint and primarey key
alter table index1 alter column eno int not null
i got the following message
ALTER TABLE ALTER COLUMN eno failed because one or more objects access this column.
March 8, 2012 at 11:04 pm
sandeep4testing (3/8/2012)
Hi Friends,I'm trying to adding the primarey key on table column. table having 7 numbers of rows,
Any one can tell me how to primarey key on table.
EX:
create table index1 (eno int,ename varchar(20),sal money)
insert index1 values(105,'ajay',9000),(103,'akil',8000),(101,'ajay',7000),(102,'ram',8500),(104,'vinad',9200)
-- now i'm trying to give not null constraint and primarey key
alter table index1 alter column eno int not null
i got the following message
ALTER TABLE ALTER COLUMN eno failed because one or more objects access this column.
you cannot add a primary key constraint to any column or group of columns that contains either NULL values or non-unique values. Makes sense, because what you're telling the database engine is that "these columns uniquely identify a record" and then because NULL is "not known", they can't be compared. So you can't just slap an index on that. So you need to fix the nulls problem first. (one way is to script the creation of the existing table and change the name before executing. Then append the data from the existing table after you fix the problems.)
March 8, 2012 at 11:15 pm
Hey sandeep try this :
alter table index1
alter column eno int not null
go
alter table index1
add constraint xafd_314 primary key (eno)
You can't add a primary key constraint by altering a column. You have to do it by adding a constraint before that you have to make that column to not null.
~SAM
March 8, 2012 at 11:40 pm
Hi SAM,
Thank you for your quick reply,
even that table not allowing to add not null constraint,
here eno column having five rows of data .
Thank's
Sandeep
March 8, 2012 at 11:52 pm
Are you using
alter column statement to change it to not null
does the eno column has any null value ??
March 9, 2012 at 12:24 am
Hi Sam,
table doesn't have any null value.
i have used below code,
alter table index1
alter column eno int not null
go
alter table index1
add constraint xafd_314 primary key (eno)
Thank's
Sandeep.
March 9, 2012 at 12:32 am
What error you are getting ??
March 10, 2012 at 4:53 am
Hi Sam,
i did a mistake there,
first i created clustered Index on eno column
again now i'm trying to add the pk on eno column that is the reason it's won't allowing pk constraint,
Thank you,
Sandeep.
March 10, 2012 at 10:23 am
Drop the clustered index (it's the dependant object that the error message referred to)
Alter the column to not null
Add a primary key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply