473,320 Members | 1,904 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Jan 9 '07 #1
9 8881
(da********@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, es****@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
Jan 9 '07 #2
Erland Sommarskog wrote:
(da********@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 :)

Jan 9 '07 #3
(da********@gmail.com) writes:
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, es****@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
Jan 9 '07 #4
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:
(da********@gmail.com) writes:
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, es****@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
Jan 10 '07 #5
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:
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?
Jan 10 '07 #6
(ot*******@yahoo.com) writes:
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.)

>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, es****@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
Jan 10 '07 #7

Erland Sommarskog wrote:
(da********@gmail.com) writes:
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 :)

Jan 10 '07 #8
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:
(ot*******@yahoo.com) writes:
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.)

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, es****@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
Jan 10 '07 #9
(ot*******@yahoo.com) writes:
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.
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, es****@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
Jan 10 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Manlio Perillo | last post by:
Hi. This post follows "does python have useless destructors". I'm not an expert, so I hope what I will write is meaningfull and clear. Actually in Python there is no possibility to write code...
1
by: Glabbeek | last post by:
I'm changing the layout of my site. Instead of using tables, I will use DIVs. It's working fine, except for 1 thing: In IE6 some DIVs are not the correct width. Mozilla and Opera are showing the...
1
by: Xiangliang Meng | last post by:
Hi, all. When reading C++ books, I'm alway confused by those terms "redefining functions", "overloading functions" and "overriding functions". Please give me some comments on those terms....
3
by: notme | last post by:
I have code with lots of "new int..." etc.. I'd like to replace every instance with "new(allocInfo) int" (that is, using placement new) I tried doing: #define new (new(allocInfo)) but that...
20
by: Vijay Kumar R. Zanvar | last post by:
Hello, Unlike register, auto keyword can not be used to declare formal parameter(s). Is there any specific reason for this? Kind regards, Vijay Kumar R. Zanvar
5
by: Samuel | last post by:
Hi, I am running into a problem of mixing UICulture = auto and allowing users to select culture using a dropdown list. I am detecting a querystring, "setlang", and when found, setting the...
5
by: maya | last post by:
at work they decided to center divs thus: body {text-align:center} #content {width: 612px; text-align:left; margin: 0 auto 0 auto; } this works fine in IE & FF, EXCEPT in FF it doesn't work if...
4
by: allan.mcrae | last post by:
As part of a very simple memory leak detector, I am trying to store the value of __FILE__ in a char*. Since gcc4.2 I get the following warning... warning: deprecated conversion from string...
4
by: Belebele | last post by:
The following code fails to compile. My intention is to provide different definitions for a nested class for a class template partial specialization. Here it is: template <typename , int class...
21
by: JOYCE | last post by:
Look the subject,that's my problem! I hope someone can help me, thanks
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.