Reply to Re: Redefining an auto-generated primary key constraint name

Your name:

Reply:


Posted by danielbuus on 01/09/07 14:42

Erland Sommarskog wrote:
> (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'
>

Hey Erland! :)

Thanks for the tip. There was a small typo, though (parentpbj instead
of parent_obj), and the select would just get all constraints on
authors and then rename the first one which wasn't necessarily the
primary key constraint, so I narrowed it down with a LIKE 'PK%'
statement.

Here's my final version, it works great, but if you spot something
stupid, don't hesitate to slap me ;)

DECLARE @pk SYSNAME SELECT @pk = name FROM sysobjects WHERE parent_obj
= object_id('authors') AND name LIKE 'PK%' EXEC sp_rename @pk,
'pk_authors'

Thanks again :)

[Back to original 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

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