Index Names not Schema Bound

I have a 2 Class tables in 2 different schemas and both have a ClassID field
as the primary key.
Staff.Class (ClassID int, ClassName varchar256))
RMS.Class (ClassID int, ClassName varchar256))
I have a primary key index on Staff.Class called pk__Class__ClassID. When I
try to create the same index on the RMS.Class table is says the name is
already used. Shouldnt the Index Name be schema bound like the table?
Hi John,
Index names are unique within the scope of the table on which it is defined.
There shouldn't be any problem having the same index name in different
tables even if they're in the same schema.
How are you creating your constraints? Below is a sample that does what
you want I think
USE AdventureWorks;
GO
CREATE SCHEMA Staff
CREATE SCHEMA RMS
GO
CREATE TABLE Staff.Class (ClassID int, ClassName varchar (256)
CONSTRAINT pk__Class__ClassID PRIMARY KEY (ClassID))
GO
CREATE TABLE RMS.Class (ClassID int, ClassName varchar (256)
CONSTRAINT pk__Class__ClassID PRIMARY KEY (ClassID))
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:6857A402-B019-4F9D-98D0-0CCB85D1E6BF@.microsoft.com...
>I have a 2 Class tables in 2 different schemas and both have a ClassID
>field
> as the primary key.
> Staff.Class (ClassID int, ClassName varchar256))
> RMS.Class (ClassID int, ClassName varchar256))
> I have a primary key index on Staff.Class called pk__Class__ClassID. When
> I
> try to create the same index on the RMS.Class table is says the name is
> already used. Shouldnt the Index Name be schema bound like the table?
|||Yes, it is possible to have both. Try this
create schema one
create schema two
create table one.class (id int constraint abc primary key, name varchar(30))
create table two.class (id int constraint abc primary key, name varchar(30))
select * from sys.objects
where name = 'abc'
create index xyz on one.class(id)
create index xyz on two.class(id)
select * from sys.indexes
where name = 'xyz'
Hope this helps,
Ben Nevarez
"John Barr" wrote:

> I have a 2 Class tables in 2 different schemas and both have a ClassID field
> as the primary key.
> Staff.Class (ClassID int, ClassName varchar256))
> RMS.Class (ClassID int, ClassName varchar256))
> I have a primary key index on Staff.Class called pk__Class__ClassID. When I
> try to create the same index on the RMS.Class table is says the name is
> already used. Shouldnt the Index Name be schema bound like the table?
|||I am simply going throug the diagrams interface and/or the design interface
in enterprise manager (sql server 2005) and attempting to create it. If you
try it, it should fail for you as well.
"Gail Erickson [MS]" wrote:

> Hi John,
> Index names are unique within the scope of the table on which it is defined.
> There shouldn't be any problem having the same index name in different
> tables even if they're in the same schema.
> How are you creating your constraints? Below is a sample that does what
> you want I think
> USE AdventureWorks;
> GO
> CREATE SCHEMA Staff
> CREATE SCHEMA RMS
> GO
> CREATE TABLE Staff.Class (ClassID int, ClassName varchar (256)
> CONSTRAINT pk__Class__ClassID PRIMARY KEY (ClassID))
> GO
> CREATE TABLE RMS.Class (ClassID int, ClassName varchar (256)
> CONSTRAINT pk__Class__ClassID PRIMARY KEY (ClassID))
>
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:6857A402-B019-4F9D-98D0-0CCB85D1E6BF@.microsoft.com...
>
>
|||I don't believe the diagrams interface (nor some of the underlying functions
for the visual designers) have been updated to understand what "schema" is,
or at least cope with it correctly, so it is probably checking against the
following logic:
if there exists a table named foo with an index named bar, return an error,
otherwise continue
If you are going to be developing with schemas I would suggest learning and
using the SQL DDL and supporting syntax instead of using diagrams and visual
helpers.
A
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:B9B22398-F27E-43F5-8F59-C25CE18AF80F@.microsoft.com...[vbcol=seagreen]
>I am simply going throug the diagrams interface and/or the design interface
> in enterprise manager (sql server 2005) and attempting to create it. If
> you
> try it, it should fail for you as well.
> "Gail Erickson [MS]" wrote:
|||I just tested creating the tables using the database diagram and also worked
for me: I have the two primary keys with the same name.
When you create the table using a database diagram you need to select the
schema from the Properties window.
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:

> I don't believe the diagrams interface (nor some of the underlying functions
> for the visual designers) have been updated to understand what "schema" is,
> or at least cope with it correctly, so it is probably checking against the
> following logic:
> if there exists a table named foo with an index named bar, return an error,
> otherwise continue
> If you are going to be developing with schemas I would suggest learning and
> using the SQL DDL and supporting syntax instead of using diagrams and visual
> helpers.
> A
>
>
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:B9B22398-F27E-43F5-8F59-C25CE18AF80F@.microsoft.com...
>
|||> I just tested creating the tables using the database diagram and also
> worked
> for me: I have the two primary keys with the same name.
> When you create the table using a database diagram you need to select the
> schema from the Properties window.
Ok, there are several items on Connect that seem to suggest that some of
this stuff is broken and won't be fixed, at least for 2008. <shrug>
In any case, it doesn't mean that using proper DDL instead of visual tools
is a bad alternative. At least you can check DDL into source control.