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?

CREATE TABLE Students
(
S_Id int NOT NULL,
LName varchar(255) NOT NULL,
FName varchar(255),
Email_Id varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LName)
)

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)?

ALTER TABLE Students
ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LName)

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

ALTER TABLE Students
DROP CONSTRAINT pk_StudentID

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

CREATE TABLE Students
(
S_Id int NOT NULL PRIMARY KEY,
LName varchar(255) NOT NULL,
FName varchar(255),
Email_Id varchar(255),
Address varchar(255),
City varchar(255)
)

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?

ALTER TABLE Students
DROP Primary Key

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

 Msg 156, Level 15, State 1, Line 2

 Incorrect syntax near the keyword ‘Primary’.

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:

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

Step 2: 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.
Step 3: So scrolling down, you will find the Constraint Name:
Step 4: Now after knowing the Constraint Name just use the simple query, which we saw earlier:
Hope you enjoyed reading this. Do comment so that I can imporve in my next post.Regards

Piyush

Hey Folks,
Do you really know, why there’s a need of Common Language Runtime (CLR) required instead of having T-SQL? Well I have some keynotes here:
• Transact-SQL is specifically designed for direct data access and manipulation in the database.
• As Transact-SQL excels at data access and management, it does not have programming constructs that make data manipulation and computation easy. Like they does not support arrays, collections, for-each loops, bit shifting, or classes.
• T-SQL is great for database code, but writing procedural code in T-SQL has always been difficult.
• So in comes the Microsoft SQL Server 2005 significantly enhances the database programming model by hosting the Microsoft .NET Framework 2.0 Common Language Runtime (CLR).
• The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code.
• This enables developers to write procedures, triggers, and functions in any of the CLR languages, particularly Microsoft Visual C# .NET, Microsoft Visual Basic .NET, and Microsoft Visual C++.
• The code that runs within the CLR is referred to as managed code.
• Managed code is better suited than Transact-SQL for calculations and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions.
• One of the benefits of managed code is type safety, or the assurance that code accesses types only in well-defined, permissible ways. Before managed code is executed, the CLR verifies that the code is safe.
• With the functionality found in the .NET Framework Library, you also have the access to thousands of pre-built classes and routines.
• By default, the common language runtime is disabled in SQL Server and must be specifically enabled using a T-SQL SET command.

Hope you like it!!!

Hello Friends,

Wass Up!!!

Welcome to WordPress.com. After you read this, you should delete and write your own post, with a new title above. Or hit Add New on the left (of the admin dashboard) to start a fresh post.

Here are some suggestions for your first post.

  1. You can find new ideas for what to blog about by reading the Daily Post.
  2. Add PressThis to your browser. It creates a new blog post for you about any interesting  page you read on the web.
  3. Make some changes to this page, and then hit preview on the right. You can always preview any post or edit it before you share it to the world.