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 :)

• Next in forum: Re: Idea: new database object that defines a FROM clause
• Prev in forum: Re: stored procedure to concatenate column value sin a single cell?
• Thread view: Re: Redefining an auto-generated primary key constraint name

[Reply to this message]