You are here: Re: Redefining an auto-generated primary key constraint name « MsSQL Server « IT news, forums, messages
Re: Redefining an auto-generated primary key constraint name

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация