Redefining an auto-generated primary key constraint name | | |
Hey there :)
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?
Thanks in advance,
Daniel Buus :)
| | | | re: Redefining an auto-generated primary key constraint name
(danielbuus@gmail.com) writes: Quote:
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/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx | | | | re: Redefining an auto-generated primary key constraint name
Erland Sommarskog wrote: Quote:
(danielbuus@gmail.com) writes: Quote:
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 :) | | | | re: Redefining an auto-generated primary key constraint name
(danielbuus@gmail.com) writes: Quote:
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.
Oops. I should have added
AND xtype = 'PK'
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx | | | | re: Redefining an auto-generated primary key constraint name
When I execute the following query:
select name
from sysobjects
where object_id('sysobjects') = 1
Why do I get all the rows instead of only one?
Erland Sommarskog wrote: | | | | re: Redefining an auto-generated primary key constraint name
I think the query fails but for some reason it returns all rows instead
of nothing. Because when I tried to run a similar query (to test) on
another server (sql Anywhere) it just did not return any rows. However,
in SQL anywhere sysobjects is implemented as a view and id is numeric
instead of int...
othell...@yahoo.com wrote: Quote:
When I execute the following query:
select name
from sysobjects
where object_id('sysobjects') = 1
Why do I get all the rows instead of only one?
>
| | | | re: Redefining an auto-generated primary key constraint name
(othellomy@yahoo.com) writes: Quote:
When I execute the following query:
select name
from sysobjects
where object_id('sysobjects') = 1
Why do I get all the rows instead of only one?
You would get either many or zero rows, depending on the object id of
sysobjects. On SQL 2000 the query returns many rows, so obviously sysobjects
has id = 1 there. On SQL 2005 you get no rows at all. (On SQL 2005
sysobjects is no longer a table.) Quote:
>Because when I tried to run a similar query (to test) on
>another server (sql Anywhere)
SQL Anywhere is an entirely different engine, alhough I can guess that
Sybase has added sysobjects & co since they acquired the product.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx | | | | re: Redefining an auto-generated primary key constraint name
Erland Sommarskog wrote: Quote:
(danielbuus@gmail.com) writes: Quote:
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.
>
Oops. I should have added
>
AND xtype = 'PK'
>
>
>
Ah! Seems more 'official' than "name LIKE 'PK%'". Thanks :) | | | | re: Redefining an auto-generated primary key constraint name
Okay. Since we are on the subject, I fail to understand why SQL Server
2K would return more than one row. If object_id('sysobejcts')
returns 1 then there is only one row with that id in sysobjects table.
I would like to look at the code of object_id() and what datatype it
returns only if I can find it! (That's a different issue)
Erland Sommarskog wrote: Quote:
(othellomy@yahoo.com) writes: Quote:
When I execute the following query:
select name
from sysobjects
where object_id('sysobjects') = 1
Why do I get all the rows instead of only one?
>
You would get either many or zero rows, depending on the object id of
sysobjects. On SQL 2000 the query returns many rows, so obviously sysobjects
has id = 1 there. On SQL 2005 you get no rows at all. (On SQL 2005
sysobjects is no longer a table.)
>
> Quote:
Because when I tried to run a similar query (to test) on
another server (sql Anywhere)
>
SQL Anywhere is an entirely different engine, alhough I can guess that
Sybase has added sysobjects & co since they acquired the product.
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx | | | | re: Redefining an auto-generated primary key constraint name
(othellomy@yahoo.com) writes: Quote:
Okay. Since we are on the subject, I fail to understand why SQL Server
2K would return more than one row. If object_id('sysobejcts')
returns 1 then there is only one row with that id in sysobjects table.
Whatever that has to do with anything. The condition is still 1 = 1. Quote:
I would like to look at the code of object_id() and what datatype it
returns only if I can find it! (That's a different issue)
There is no code stored in SQL Server for tables. The easiest way to
see the column definitions is sp_help.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |  |
Similar Microsoft SQL Server bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,501 network members.
|
