Posted by Erland Sommarskog on 01/09/07 13:08

(danielbuus@gmail.com) writes:
> I'm using Ruby on Rails to create a migration of a legacy database on
> an MS SQL Server. I'd like to name my constraints myself, such as
> 'pk_authors', but in the cases where a table has an auto incremented
> IDENTITY(1,1) id field, Rails takes over and adds the primary key
> itself when creating the table.
>
> This is fine, except then the constraint gets a name like
> 'PK__authors_384934' which is not very intuitive or easily remembered
> ;)
> I'd like to just throw in an 'ALTER TABLE authors...' statement just
> after that table has been created, but I'm not sure how to go about
> renaming the auto generated constraint since the name it gets is
> partially random. Is there any way to indirectly refer to the
> constraint like 'RENAME PRIMARY KEY CONSTRAINT ON authors TO
> pk_authors' or something like that, so that I can rename the
> constraint?

DECLARE @pk sysname
SELECT @pk = name FROM sysobjects WHERE parentpbj = object_id('authors')
EXEC sp_rename @pk, 'pk_authors'

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]