Hello Folks,

We also know some basics about the Primary Key.

The Primary Key constraint uniquely identifies each record in a database table. Primary Keys must contain unique values. By default, Primary key creates a clustered index on the column on which they are defined.  A Primary key column cannot contain NULL values.

There are multiple ways of creating, altering and deleting Primary Key Constraint on a table:

How to create a PK with a constrain name while creating a table?

Note that in the above code, we are explicitly specifying the name of the PK constraint.

How to add a PK constraint later (after the table was created)?

So, how do you remove it? Well, its simple:

But what if you created a table with PK like this:

Note that PK constraint is created in the above code but you have not specified any constraint name. Now how do you drop it without the name?

Will this work?

Well, not :( – this works MySQL but not in SQL Server. And the error message is very interesting too :)

Well don’t worry; we have a solution for this:

As you have seen so far that if you know the name of Primary Key Constraint, you can easily be drop it.

But in the above case, SQL Server has automatically/implicitly assigned a name to this PK constraint. So the question is how to find that out? Well there are multiple ways again. Quickest could be:

See these few steps carefully:

  • In your SQL Query Window, write the name of the Table in which you have declared Primary Key and Select it.

1_Dropping_a_Primary_Key_Constraint_in_SQL_Server_is_it_easy_May_be_May_be_not!

  • Press ALT + F1 on your Keyboard. This is mainly being done to get all the details of the Students table. They are being displayed under the Results tab.

2_Dropping_a_Primary_Key_Constraint_in_SQL_Server_is_it_easy_May_be_May_be_not!

3_Dropping_a_Primary_Key_Constraint_in_SQL_Server_is_it_easy_May_be_May_be_not!

  • So scrolling down, you will find the Constraint Name:

4_Dropping_a_Primary_Key_Constraint_in_SQL_Server_is_it_easy_May_be_May_be_not!

  • Now after knowing the Constraint Name just use the simple query, which we saw earlier:

5_Dropping_a_Primary_Key_Constraint_in_SQL_Server_is_it_easy_May_be_May_be_not!

Hope you enjoyed reading this. Do comment so that I can imporve in my next post.

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook