2

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.

3

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
);
  • Thanks for so quick answer. One more doubt , if I have like thousands of tables in a database , I have to give all unique constraints name? – user7511549 Mar 28 '17 at 20:51
  • Yes, all constraints in any given database must be uniquely named. – Max Vernon Mar 28 '17 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.