removing primary key - SQL Server Forums

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

removing primary key

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2005-11-27 : 05:43:23

Hi How do i remove the primary key for this table

create table table2
(col1 int,
col2 int,
primary key(col2))

Thanks
Vic

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-27 : 06:36:16

You'd have to know the name of the primary key constraint. If you created the table without giving the primary key an explicit name, then SQL Server will have given it an arbitrary name. However, given that you can only have one primary key on a table, it's pretty simple to find that name from the INFORMATION_SCHEMA views:


DECLARE @pkname sysname

SELECT @pkname = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'dbo'
  AND TABLE_NAME = 'table2'
  AND CONSTRAINT_TYPE = 'PRIMARY KEY'

IF @pkname IS NOT NULL
BEGIN
  PRINT @pkname
  EXECUTE ( 'ALTER TABLE table2 DROP CONSTRAINT ' + @pkname )
END ELSE BEGIN
  PRINT 'No primary key'
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-27 : 23:31:20

or

Use Enterprise Manager and remove it

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources