A primary key is a fundamental concept in relational database design. It is a column, or a combination of columns, in a table that uniquely identifies each row in that table. The primary key ensures that each record in the table is unique and can be uniquely retrieved, updated, or deleted. So it makes the a record unique

1. Uniqueness: Each value in the primary key column(s) must be unique. No two rows can have the same primary key value.
2. Non-nullable: A primary key column cannot contain NULL values. Every row must have a value for the primary key.
3. Immutable: Once a primary key value is assigned, it should not change. This ensures the stability and integrity of references to that key from other tables.
4. Single or Composite: A primary key can consist of a single column (simple key) or multiple columns (composite key). A composite key is used when no single column is unique enough to act as a primary key.

• Uniqueness Enforcement: Primary keys enforce the uniqueness of rows in a table, ensuring that each record can be distinctly identified.
• Indexing: When a primary key is created, SQL Server automatically creates a unique index on the primary key column(s), which improves query performance. We will talk about indexing in a following post.
• Referential Integrity: Primary keys are used to define relationships between tables. Foreign keys reference primary keys to maintain referential integrity across the database. When you have a foreign key on a table, the table is sometimes called the child table. So for example if we want to link orders to customers, the orders table might have a foreign key column, which we will call Customer ID, which will match the primary key column of the customer table – which in my case I would also have called CustomerID. We will talk all about Foreign Keys in the next post

Consider a table called Customers designed to store customer information. A suitable primary key for this table could be a column named CustomerID:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(50),
    PhoneNumber VARCHAR(15),
    Address VARCHAR(100)
);

In this example:

• CustomerID is the primary key.
• It uniquely identifies each customer.
• It ensures that no two customers can have the same CustomerID.

Get SQL Server to create the Primary Key Values for you

When working with SQL Server, you often need a way to generate unique values for a primary key column. The IDENTITY property is a convenient way to achieve this. It automatically generates unique values for a column, and is typically used for primary keys. If we wanted to use it on the above example we need to make a small tweak to the code

CREATE TABLE Customers (
	CustomerID INT IDENTITY(1, 1) PRIMARY KEY
	,Name VARCHAR(50)
	,Email VARCHAR(50)
	,PhoneNumber VARCHAR(15)
	,Address VARCHAR(100)
	);

Creating a primary key in SSMS can be done during table creation or by modifying an existing table. Here’s how to do it:

1. Open SQL Server Management Studio: Launch SSMS and connect to your SQL Server instance.

2. Connect to the Database Engine: In the “Connect to Server” window, select the appropriate server type, server name, and authentication method, then click “Connect.”

3. Expand the Database: In the Object Explorer, expand the database where you want to create the new table.

4. Navigate to Tables: Right-click the “Tables” folder and select “New Table…”

5. Define Columns: In the table designer, define the columns for your table:

   • Enter the column name (e.g., CustomerID).
   • Select the data type (e.g., INT).
   • Uncheck the “Allow Nulls” box for the primary key column.

6. Set Primary Key: Right-click the row selector (the box to the left of the column name) for the column you want to set as the primary key (e.g., CustomerID) and select “Set Primary Key.”

7. Save the Table: Click “Save” in the toolbar, enter a name for your table (e.g., Customers), and click “OK.”

1. Open SQL Server Management Studio: Launch SSMS and connect to your SQL Server instance.

2. Connect to the Database Engine: In the “Connect to Server” window, select the appropriate server type, server name, and authentication method, then click “Connect.”

3. Expand the Database: In the Object Explorer, expand the database containing the table you want to modify.

4. Navigate to Tables: Expand the “Tables” folder, right-click the table you want to modify, and select “Design.”

5. Set Primary Key: In the table designer, right-click the row selector for the column you want to set as the primary key (e.g., CustomerID) and select “Set Primary Key.”

6. Save the Changes: Click “Save” in the toolbar to apply the changes.

By understanding and correctly implementing primary keys, you can ensure the uniqueness and integrity of your data, which is crucial for the reliable operation of your SQL Server database.

Conclusion

Understanding primary keys is a fundamental step in mastering SQL Server and ensuring the integrity of your database. But there’s so much more to learn! Stay ahead in your SQL journey by subscribing to our newsletter. Get the latest tips, tutorials, and best practices delivered straight to your inbox. Don’t miss out on the next post in our series, where we’ll dive into the critical concept of foreign keys and their role in maintaining referential integrity.

Subscribe now and take your SQL Server skills to the next level!

Subscribe to Our Newsletter