About the uniqueness of object naming in SQL Server database. For example, database objects such as tables, indexes, constraints, etc. sometimes, DBA s often need to create or rename objects when doing database maintenance. At this time, they will encounter a problem, the uniqueness of object naming. Although it's a small question, it's not clear to many people. Before reading the article, you can answer a few questions first

 

1: Is the table name unique in the database? Can a table with the same name exist in different schemas?

 

2: Is the index name unique in the database? Can a table with the same name exist in different schemas?

 

3: What about other database objects? For example, constraints, triggers.

 

 

Let's take a look at the experimental example. As shown below, there is a table named "Production.Product" in AdventureWorks 2014 database

 

USE AdventureWorks2014;

GO

SELECT * FROM sys.objects WHERE OBJECT_ID =OBJECT_ID('Production.Product')

 

 

When creating a table with the same name, you will encounter the error "There is already an object named 'Product' in the database.". As follows:

 

CREATE TABLE Production.Product

(

    name  NVARCHAR(32)

)

 

 

 

In addition, if I create this table object under the default Scheme (dbo), then this is no problem. As follows:

 

CREATE TABLE dbo.Product

(

    name  NVARCHAR(32)

)

 

 

 

Conclusion summary: the table name is unique in the database. Under the same Schema, the same table name is not allowed, but under different schemas, a table with the same name can exist. This is a good understanding. In real life, there are many such examples. For example, university dormitories, 11 dormitories have 201 named dormitories, and 13 dormitories have 201 named dormitories.

 

Next, we generate a script of 'Production.Product', and modify the table name as product. When executing the script, we will encounter an error prompt that the constraint already exists (note that all the error prompts that the constraint already exists will not be displayed at once).

 

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [Production].[Product_2020](

    [ProductID] [int] IDENTITY(1,1) NOT NULL,

    [Name] [dbo].[Name] NOT NULL,

    [ProductNumber] [nvarchar](25) NOT NULL,

    [MakeFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Product_MakeFlag]  DEFAULT ((1)),

    [FinishedGoodsFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Product_FinishedGoodsFlag]  DEFAULT ((1)),

    [Color] [nvarchar](15) NULL,

    [SafetyStockLevel] [smallint] NOT NULL,

    [ReorderPoint] [smallint] NOT NULL,

    [StandardCost] [money] NOT NULL,

    [ListPrice] [money] NOT NULL,

    [Size] [nvarchar](5) NULL,

    [SizeUnitMeasureCode] [nchar](3) NULL,

    [WeightUnitMeasureCode] [nchar](3) NULL,

    [Weight] [decimal](8, 2) NULL,

    [DaysToManufacture] [int] NOT NULL,

    [ProductLine] [nchar](2) NULL,

    [Class] [nchar](2) NULL,

    [Style] [nchar](2) NULL,

    [ProductSubcategoryID] [int] NULL,

    [ProductModelID] [int] NULL,

    [SellStartDate] [datetime] NOT NULL,

    [SellEndDate] [datetime] NULL,

    [DiscontinuedDate] [datetime] NULL,

    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Product_rowguid]  DEFAULT (newid()),

    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Product_ModifiedDate]  DEFAULT (getdate()),

 CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED 

(

    [ProductID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

 

 

Msg 2714, Level 16, State 5, Line 11

There is already an object named 'PK_Product_ProductID' in the database.

Msg 1750, Level 16, State 0, Line 11

Could not create constraint or index. See previous errors.

 

 

As shown above, constraints are unique, just like table names. It is also related to Schema. As shown below, the following SQL is OK.

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Product_2020](

    [ProductID] [int] IDENTITY(1,1) NOT NULL,

    [Name] [dbo].[Name] NOT NULL,

    [ProductNumber] [nvarchar](25) NOT NULL,

    [MakeFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Product_MakeFlag]  DEFAULT ((1)),

    [FinishedGoodsFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Product_FinishedGoodsFlag]  DEFAULT ((1)),

    [Color] [nvarchar](15) NULL,

    [SafetyStockLevel] [smallint] NOT NULL,

    [ReorderPoint] [smallint] NOT NULL,

    [StandardCost] [money] NOT NULL,

    [ListPrice] [money] NOT NULL,

    [Size] [nvarchar](5) NULL,

    [SizeUnitMeasureCode] [nchar](3) NULL,

    [WeightUnitMeasureCode] [nchar](3) NULL,

    [Weight] [decimal](8, 2) NULL,

    [DaysToManufacture] [int] NOT NULL,

    [ProductLine] [nchar](2) NULL,

    [Class] [nchar](2) NULL,

    [Style] [nchar](2) NULL,

    [ProductSubcategoryID] [int] NULL,

    [ProductModelID] [int] NULL,

    [SellStartDate] [datetime] NOT NULL,

    [SellEndDate] [datetime] NULL,

    [DiscontinuedDate] [datetime] NULL,

    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Product_rowguid]  DEFAULT (newid()),

    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Product_ModifiedDate]  DEFAULT (getdate()),

 CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED 

(

    [ProductID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

 

 

Conclusion: for any constraint (primary key constraint, foreign key constraint, default constraint, etc.), it is unique under the same Schema in the database, and it is not allowed to have the same constraint name, but under different schemas, there can be constraints with the same name.

 

 

For index, if you test it briefly, you will find that the situation is a little different.

 

 

 

USE [AdventureWorks2014]

GO

CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_rowguid] ON [Production].[Product_2020]

(

    [rowguid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

 

 

Why is the uniqueness of the index object different from the previous table object? To be honest, I haven't seen the detailed introduction. I can only suspect that it has something to do with the official settings and restrictions of SQL Server.

 

 

Can an index with the same name exist in SQL Server? What about the same table?

 

Answer: different tables can have indexes with the same name, but the same table cannot have indexes with the same name.

 

 

For triggers, it has the same rules as table names and constraints. This is limited to space, so we will not carry out it. If you are interested, please test yourself.

 

 

The following is a table summary of the previous knowledge:

 

	Under the same Schema	Under different schemes
Table	An object with the same name cannot appear	Objects with the same name can appear
Constraint	An object with the same name cannot appear	Objects with the same name can appear
Index	An object with the same name can appear, but an index with the same name cannot exist for the same table	Objects with the same name can appear
Trigger	An object with the same name cannot appear	Objects with the same name can appear