Remove 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

SQL Server 2000 Forums

Transact-SQL (2000)

Remove primary key

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2004-03-11 : 12:09:03

We have a couple of tables where I need to remove the primary key and create a new one. To spread it across several dbs I need to write a SQL script.

This is what I wrote:


ALTER TABLE tblMandats
DROP CONSTRAINT aaaaatblMandats_PK
GO


ALTER TABLE tblMandats
ADD CONSTRAINT pktblMandats  PRIMARY KEY (IDMandat)
GO

My problem with that code is that we noticed that sometimes the constraint has got a different name, for example 'aaaaatblMandats1_PK', therefore the script fails to drop and of course to create the new primary key.

Is there a way I could use to retrieve the constraint name of the primary key so my DROP will be succesful ?

jean-luc
www.corobori.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-11 : 12:38:50

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE...

The INFORMATION_SCHEMA views will give you what you need.

Tara

Corobori
Posting Yak Master

105 Posts

Posted - 2004-03-11 : 13:01:07

Great, I am getting there.

Still I am getting an error in my script:


DECLARE @CST VARCHAR(50)

SELECT @CST=CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='tblMandats'
AND CONSTRAINT_TYPE='PRIMARY KEY'

ALTER TABLE tblMandats
DROP CONSTRAINT @CST
GO

The message:

Server: Msg 170, Level 15, State 1, Line 9
Ligne 9 : syntaxe incorrecte vers '@CST'.

This is the DROP CONSTRAINT line

jean-luc
www.corobori.com

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-11 : 13:09:39

I don't know if I'd reccomend this type of approach...but


DECLARE @sql varchar(800)
SELECT @SQL = 'ALTER TABLE tblMandats DROP CONSTRAINT' + @CST
EXEC(@SQL)
GO

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-11 : 13:12:17

jean-luc, what you are trying to do is dynamic sql. I would not recommend using it as Brett mentioned. Explicitly write out your commands.

Tara

Corobori
Posting Yak Master

105 Posts

Posted - 2004-03-11 : 13:14:00

Ok guys, thanks a lot it's working now.

jean-luc
www.corobori.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources