• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Adding Primary Keys Dynamically

I am trying to add a primary key to 160 tables with dynamic sql

select @vssql = 'ALTER TABLE ' + @vstablename + '_Archive' +  ' ADD CONSTRAINT ' +  'pk_' + @PKColName  + ' PRIMARY KEY ' +'(' + @PKColName + ')'

exec(@vssql)

Many of the tables have a primary key column with the same name.  for example recordid is very popular.  I am hitting errors when running my script and sql complains that there is already a object name pk_recordid in the database.  

I am assuming that it doesn't want to create the same primary key name for multiple tables.  Is there anyway around this?

Thanks for your help.
0
sherbug1015
Asked:
sherbug1015
1 Solution
 
Scott PletcherSenior DBACommented:
>> Is there anyway around this? <<

Prefix the constraint name with the table name too, before the 'pk_...'.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now