Prev: SQL 2008 - What happened to the "Show ... Pane"
Next: SQL Azure Forum on MSDN
From: ben brugman on 2 Apr 2010 09:44
Dear reader,

For primairy key names I was (and still am) used to names like :
1.
PK_<table_name>

Now I have a database which has primairy keys with double underscores and
names like :
2.
PK__<table_name>__<number>

There are two underscores and a large number.

When scripting (database or table): The name from 1 appears in the script.
The name from 2 does not appear in the script and a similar name with a
different number is generated when the script is executed.

My problem, if I do not have control over the names of objects, it becomes
difficult to make maintenance scripts.


My questions:
How did I get the names of type 2 in the first place ?
Can I get rid of this type of names ?
How to do maintenance for example how do you drop such a constraint ?

Or in general am I doing something wrong with the primairy keys or
maintenance ?


Example of a drop script:
DROP CONSTRAINT [PK__compa_values__FDC3458430F848ED]
Problem is for each installation the number wil be different.

Thanks for your time and attention,
Ben Brugman


From: Plamen Ratchev on 2 Apr 2010 10:22
It is not a good practice to depend on SQL Server to generate constraint names. It is much better to define the
constraints with proper name, like:

CREATE TABLE Foo (
fookey INT NOT NULL CONSTRAINT pk_foo PRIMARY KEY,
datacol CHAR(1) NULL);

Then you can drop then referencing the correct name.

--
Plamen Ratchev
http://www.SQLStudio.com
From: TheSQLGuru on 2 Apr 2010 11:48
Makes source code control much easier too! :-)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:doadnUi2S-USYCjWnZ2dnUVZ_jSdnZ2d(a)speakeasy.net...
> It is not a good practice to depend on SQL Server to generate constraint
> names. It is much better to define the constraints with proper name, like:
>
> CREATE TABLE Foo (
> fookey INT NOT NULL CONSTRAINT pk_foo PRIMARY KEY,
> datacol CHAR(1) NULL);
>
> Then you can drop then referencing the correct name.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Ajay Rengunthwar on 4 Apr 2010 08:17
On Apr 2, 10:48 am, "TheSQLGuru" <kgbo...(a)earthlink.net> wrote:
> Makes source code control much easier too!  :-)
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Plamen Ratchev" <Pla...(a)SQLStudio.com> wrote in message
>
> news:doadnUi2S-USYCjWnZ2dnUVZ_jSdnZ2d(a)speakeasy.net...
>
>
>
> > It is not a good practice to depend on SQL Server to generate constraint
> > names. It is much better to define the constraints with proper name, like:
>
> > CREATE TABLE Foo (
> >  fookey INT NOT NULL CONSTRAINT pk_foo PRIMARY KEY,
> >  datacol CHAR(1) NULL);
>
> > Then you can drop then referencing the correct name.
>
> > --
> > Plamen Ratchev
> >http://www.SQLStudio.com- Hide quoted text -
>
> - Show quoted text -

Always good to have these standards in place while creating the
database objects.
From: ben brugman on 8 Apr 2010 08:56
Thank you, Plamen Ratchev, TheSQLGuru and Ajay Rengunthwar,

I do agree with you three that it is much better practise to have proper
names.
Regretfully not all databases do have the proper names, that's why I asked
the question.

Have found/created some code to dynamically find the old name and drop the
constraint and rebuild the constraint with a new new. But now I have to
remove all referential constraints which use the PK constraint. So allthough
it is not practical to have no proper names, getting rid of them is also not
practical.

Thanks for your time and attention,
Ben Brugman



"Ajay Rengunthwar" <ajudba(a)gmail.com> wrote in message
news:d043ff18-0bd0-45a1-893e-15af2fd9440d(a)g10g2000yqh.googlegroups.com...
On Apr 2, 10:48 am, "TheSQLGuru" <kgbo...(a)earthlink.net> wrote:
> Makes source code control much easier too! :-)
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Plamen Ratchev" <Pla...(a)SQLStudio.com> wrote in message
>
> news:doadnUi2S-USYCjWnZ2dnUVZ_jSdnZ2d(a)speakeasy.net...
>
>
>
> > It is not a good practice to depend on SQL Server to generate constraint
> > names. It is much better to define the constraints with proper name,
> > like:
>
> > CREATE TABLE Foo (
> > fookey INT NOT NULL CONSTRAINT pk_foo PRIMARY KEY,
> > datacol CHAR(1) NULL);
>
> > Then you can drop then referencing the correct name.
>
> > --
> > Plamen Ratchev
> >http://www.SQLStudio.com- Hide quoted text -
>
> - Show quoted text -

Always good to have these standards in place while creating the
database objects.


 | 
Pages: 1
Prev: SQL 2008 - What happened to the "Show ... Pane"
Next: SQL Azure Forum on MSDN