I am trying to create a duplicate table in SQL SERVER. I changed the table name but all other objects like constraints are same. Do i have to change all the objects as they exists in the base table or i can have same objects name? I know it might sound a silly question but i am new to this.
Explicitly named constraints must be uniquely named within a database.
If they are not unique, you'll see errors like this:
Msg 2714, Level 16, State 5, Line 10
There is already an object named 'PK_SomeTable' in the database.
Msg 1750, Level 16, State 0, Line 10
Could not create constraint. See previous errors.
This is especially pertinent for creating #temp tables. Never specify the name of a constraint when creating a #temp table, since concurrent executions of the code will fail with the above error.
Take for example:
CREATE TABLE dbo.SomeTable ( ID int NOT NULL CONSTRAINT PK_SomeTable PRIMARY KEY CLUSTERED ); CREATE TABLE dbo.SomeTable2 ( ID int NOT NULL CONSTRAINT PK_SomeTable PRIMARY KEY CLUSTERED );
The command fails because
PK_SomeTable is defined twice, once in each table. However, if we re-code the 2nd statement:
CREATE TABLE dbo.SomeTable2 ( ID int NOT NULL CONSTRAINT PK_SomeTable2 PRIMARY KEY CLUSTERED );
Now, it works.
For #temp tables, you'd use the following construct:
CREATE TABLE #SomeTable ( ID int NOT NULL PRIMARY KEY CLUSTERED );