how to remove a 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

how to remove a primary key

Author

Topic

newman
Starting Member

7 Posts

Posted - 2005-08-15 : 14:45:49

hello friends!...

I am always workign in Query Analyzer..

I hav a created a table with a primary key set to its first column..

Now i want to remove the key from the column..

Is there any query to remove the key that has been already set to a column???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-15 : 14:53:58

Yes you can use ALTER TABLE DROP CONSTRAINT. Check SQL Server Books Online for details.

Tara

sobin
Starting Member

3 Posts

Posted - 2005-09-06 : 08:19:33

Hi all,

I hav created a table like,

create table emp (idno integer primary key, ename varchar(20))

Now, how do I remove the primary key constraint?????

Help me

thanx

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-06 : 09:06:13

a) read the previous answer!!!!
b) follow it's instructions!!!!
c) cheat (but don't learn) and do it in Enterprise Manager.

sobin
Starting Member

3 Posts

Posted - 2005-09-09 : 13:15:03

Hi,

I m not getting the right query to do it. Can u show me the query for that??

I dont want to do it thru enterprise manager.

Thanx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-09 : 13:16:05

Sobin,

Check out the examples in the ALTER TABLE topic in SQL Server Books Online. If you do the change from EM, but click the save change script button without saving the change, then you can see the exact script to use in Query Analyzer.

Tara

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 13:27:04

Hi sobin, Welcome to SQL Team!

In Enterprise Manager RightClick the table and choose "Design Table"

Click the "Key" Icon in the toolbar - UNTIL it is popped out, and none of the columns have a key next to it. (This will remove the primary key from the table)

Press the "floppy disk" Icon to save the changes.

Alternatively highlight the column(s) for a new Primary key and press the "Key" Icon - that will re-assign the highlighted columns to become the Primary Key - then press Save.

Guys ...

"ALTER TABLE DROP CONSTRAINT"
...
"a) read the previous answer!!!!
b) follow it's instructions!!!!
c) cheat (but don't learn) and do it in Enterprise Manager"

this is the "New to SQL Server" forum, I think your answers are a bit "User Hostile"!

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-09 : 13:27:38

BOL doesn't have an example of that, and besides, they have to know the name of the constraint...they may or may not know this



SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'emp'

ALTER TABLE dbo.emp
	DROP CONSTRAINT <CONSTRAINT_NAME>

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-09 : 13:32:29

quote:
Originally posted by Kristen

this is the "New to SQL Server" forum, I think your answers are a bit "User Hostile"!

I disagree. My posts give enough information for the person to find ways to solve the problem. It's not about providing the exact solution in this forum but rather leading them down the correct path to improve their SQL Server knowledge. That's not to say that providing exact answers isn't the way to go either, but someone who is dropping a PK is probably filling a DBA role where we expect a little more from them.

Tara

sobin
Starting Member

3 Posts

Posted - 2005-09-09 : 13:36:35

Hi all,

Dont quarrel each other. I am not a DBA, just an entry level software programmer. But i wanted 2 learn all these stuffs.

Thanx for great comments n replies.

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 13:53:49

"Dont quarrel each other"

Not my intention, and I've known Tara for quite a while now, so I can doubt she was picking a quarrel either!

Tara: that said I think you could/should elaborate a little more in here. For example, I'm sure you would have done a great job broadening the conversation to the dangers of having NO Primary Key!

Bretts post was good on getting the Constraint Name ... so all-in-all there is a fair bit of useful information in here for sobin.

Apologies for getting anxious when a newcomer arrives and I see them getting some Guru answers!

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-09 : 19:59:37

DBA's in general like to quarrel anyway, especially when developers are involved.

So.....this is a good real-life lesson for you anyway sobin. lol

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

homebrew
Posting Yak Master

114 Posts

Posted - 2005-09-12 : 14:16:26

From Tara " .... If you do the change from EM, but click the save change script button without saving the change, then you can see the exact script to use in Query Analyzer."

This is a pretty useful feature that I use now & then ... check it out. It's also useful when you make changes in a test environment, because you can generate and SAVE the script of what you just did, then when you need to do the same thing in Production, you get your saved script & run it, ensuring that it's exactly the same as what you ran in test.

shanku
Starting Member

1 Post

Posted - 2008-04-04 : 06:30:39

Hello every body I am new in this forum I create a table at SQL Server 2005

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PATCHLOG]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PATCHLOG](
[PATCHID] [varchar](25) NOT NULL,
[DESCRIPT] [varchar](50) NULL,
[EXECUTEDT] [datetime] NULL,
[EXECUTETM] [numeric](6, 0) NULL,
[CREATEDT] [datetime] NULL,
[STATUS] [varchar](10) NULL,
PRIMARY KEY CLUSTERED
(
[PATCHID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

After create this table I could not drop the primary key Please help

Srikanto

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-04-04 : 08:16:11

First thing you're going to need to do is find the name of the primary key. This query should get you that

SELECT name FROM sys.objects WHERE TYPE = 'pk' AND parent_object_id = OBJECT_ID('PATCHLOG')

Once you have the name of the PK, you should be able to drop it as follows

ALTER TABLE PATCHLOG DROP CONSTRAINT <Primary key name here>

If you have any foreign key constraints referencing the table, you'll have to drop them first.

p.s. Shanku, you're probably better off creating a new thread than posting at the end of an old one. That way people are less likely to overlook it.

Gail Shaw

Have a nice day, unless you have other plans

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-04 : 09:25:23

Gila,

So you found the answer after 3 years!!!!!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-04-04 : 10:12:58

I make it 1 hour, 45 min.
Check the date of Shanku's post.

Gail Shaw

--
Have a nice day, unless you have other plans

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-04-04 : 10:25:47

Don't really think that Tara was being hostile, but I do think that some people need to remember that this is the New to SQL Server forum and therefore most people post on here are not DBA's and just using SQL Server for one reason or another

another way could of been using the sp_help to find the primary key name

sp_help 'customers'

Alter table Customers drop constraint 'PK_CustomerID'

all different method are welcomed by most people, after being attacked myself on one of my first ever posts I know the feeling

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-06 : 05:56:13

Hi,

Try like this

Right click on tables then click 'design' then we table design and then in primary key column just right click the column and then click remove primary key

Be happy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources