Adding a column to a 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

SQL Server 2005 Forums

Transact-SQL (2005)

Adding a column to a primary key

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-07-01 : 17:45:23

Hi
I want to make col2 primary key as well and col1 is a already primary key in the table.
How do i do it?
thnx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-01 : 18:40:08

You'll need to drop the existing PK and then create it with both columns.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-01 : 18:40:31

Let me know if you want the syntax for it, or you can use the GUI to do the work.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

marcodelphi
Starting Member

4 Posts

Posted - 2010-07-01 : 18:44:15

Hi,

Try to drop the primary key, than recreate it:


ALTER TABLE [dbo].[table1]
DROP CONSTRAINT [PK__table1]

WHERE "PK_table1" is the name of the key.


ALTER TABLE [dbo].[table1]
ADD CONSTRAINT [PK__table1] 
PRIMARY KEY CLUSTERED ([col1,col2])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

Let me know if it worked.

Bye!

Marco André

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources