Primary Keys - ( SQL Server Keys )

In SQL server, a Primary Key is a column or a group of columns that uniquely identify a row in a table. SQL Server allows creating a Primary Key by using the Primary Key Constraint. Primary keys are essential in a database as they ensure that the data is accurately organized and consistent.

Syntax

SQL Server allows for various ways to create a primary key. Here are three methods to create a primary key:

1. Method one: using CREATE TABLE statement:

CREATE TABLE table_name(Col1 datatype PRIMARY KEY(Col1));

2. Method two: using ALTER TABLE statement:

ALTER TABLE table_name ADD PRIMARY KEY (Col1);

3. Method three: using the SQL Server Management Studio (SSMS):

• Open the SSMS
• Navigate to the Object Explorer > your database > Tables > your table's node > Keys.
• Right-click on Keys and select "New Primary Key"
• Select the table and column(s) you want to set as the primary key.
• Review the Primary Key options before clicking "OK."

Example

Here's an example of how to create a primary key using CREATE TABLE statement:

CREATE TABLE employees
(
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(255),
  hire_date DATE
);

Here's an example of how to create a primary key using ALTER TABLE statement:

ALTER TABLE employees ADD PRIMARY KEY (id);

Here's an example of how to create a primary key using the SQL Server Management Studio (SSMS):

• Open the SSMS
• Navigate to the Object Explorer > your database > Tables > employees
• Right-click on the "employees" table and select "Design"
• Select the "id" column
• In the "Column Properties" window, find the "Primary Key" option and click the checkbox
• Save the changes

Output

Once a primary key constraint is created, SQL server automatically creates a unique index and ensures that duplicate values cannot exist in the column(s) listed as primary key column(s).

Explanation

Primary keys are essential in database design because they ensure that the data in a table is accurately organized and consistent. The SQL Server database engine creates a unique index and ensures that no two rows in a table can have the same values in the primary key column(s).

Use

Primary keys help to maintain database integrity. A primary key constraint ensures that the column designating the primary key has a unique value for each row. This constraint makes sure that there are no duplicate rows in the table, thus preventing data inconsistency and integrity problems.

Important Points

Here are a few essential points to keep in mind while working with Primary Keys.

• Only one primary key is allowed per table.
• Primary keys cannot be modified or deleted if there is a foreign key constraint connected to them.
• Primary keys must have unique data entries (no two rows can have the same value in a primary key column).
• A primary key column cannot have NULL values.

Summary

In this article, we discussed primary keys in SQL Server. We covered the syntax for creating primary keys using both SQL statements and the SQL Server Management Studio. Additionally, we discussed how to use primary keys to maintain database integrity, and essential points to keep in mind while working with primary keys.