How to add new column and make it Primary Key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to add new column and make it Primary Key

Author

Topic

amit.2601
Starting Member

8 Posts

Posted - 2010-06-23 : 06:10:34

Hi All,
I have table having 4 column and having 100 of records.
Now i have to add one more column and want to make it a primary key.

What i have done ,i have added one more column and make it Primary key,but when i saved the table it shows primary key doednot have null value and i have primary key column value null against 100 records which is already present .

Kindly suggests what should i do to perform my requirement.

Regards
Amit

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-23 : 06:13:25

Update the new column so that it contains non-null unique values. Once you've done that, then you can make it the primary key.

--
Gail Shaw
SQL Server MVP

amit.2601
Starting Member

8 Posts

Posted - 2010-06-23 : 06:27:37

How it could be updated will you please elaborate further

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-23 : 06:50:03

Use the UPDATE statement, populate the column with values. What values you populate it with is your decision.

Columns that are part of a primary key cannot be null, and the combination of all the columns must be unique. If you're creating a single column primary key, the values in that column must be unique.

--
Gail Shaw
SQL Server MVP

amit.2601
Starting Member

8 Posts

Posted - 2010-06-23 : 07:05:29

That is where problem reside, i have 100 records already and when i have created new column then all the value of this column would be null then how i t could be a pk

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-23 : 07:10:27

Add an identity column to the existing table then you can add primary key constraint.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-23 : 07:15:39

quote:
Originally posted by amit.2601

i have 100 records already and when i have created new column then all the value of this column would be null then how i t could be a pk

You cannot make it the primary key until the column has unique values in it.

You can make it an identity column, or you can do an update with the row_number function (assuming it's an int), but you must ensure that all rows have a value (and a unique value) before you can make it the primary key.

You also need to ensure that, if you don't go the identity route, that any new rows specify a unique value for the pk column.

--
Gail Shaw
SQL Server MVP

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-23 : 07:19:20

quote:
Originally posted by pk_bohra

Add an identity column to the existing table then you can add primary key constraint.

I am here to learn from Masters and help new bees in learning.

An example for explanation:

Create table test
( Sname varchar(50),
Sdate datetime)

Insert into test
Select null, getdate() union
Select 'TTT', getdate() union
Select null, getdate() union
Select 'SSS', getdate() union
Select 'jjj', getdate()

Alter table test
Add Srno int identity

Alter table test
Add constraint PK_Test primary key (Srno)

Regards,
bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources